CODEWITHSUNDEEP
arraysexcelif-statementaverage
KRD
KRD

Reputation: 107

Excel average based on row and column values

I would like to calculate the average (and other summary statistics) of algae counts based on the algal group (Column B) and the month (collection dates are in row 1). The first few columns and rows look like this:

row B---------------------C-------------------D

Algal Group -------6/5/2000 ----------7/5/2000-----etc.

Cyanobacteria-------5---------------------60        
Bacillariophyta-------2---------------------40      
Xanthophyta-------[blank]---------------30

I've tried using the standard AVERAGEIFS combined with the MONTH forumla, but I think the row of dates is throwing it off. I've also tried inserting a new row below the dates (i.e., row 2) with the Month of the collection date in row 1, and referring to that column in an AVERAGEIFS. That didn't work either. I've also tried entering it as an array formula. The dates are C1:DW1, Algae Groups are B2:B246. Any thoughts are much appreciated!

Upvotes: 0

Views: 5152

Answers (1)

Scott Craner
Scott Craner

Reputation: 152505

Use an array formula like this:

=AVERAGE(IF((MONTH($C$1:$E$1)=H8)*($B$2:$B$4=H9)*($C$2:$E$4<>""),$C$2:$E$4))

Being an Array it needs to be confirmed with Ctrl-Shift-Enter. If done correctly Excel will put {} around the formula.

enter image description here

Upvotes: 1

Related Questions