Average If Problem

  • Thread starter Thread starter Kris Taylor
  • Start date Start date
K

Kris Taylor

Hi all,

Here is my current dilemna. I have a bunch of dates in column A (in
02/31/2004 format) and random numbers in column B (Some cells contain
N/A or contain nothing).

What I need is a formula that will average the numbers in B for each
associated month for the year 2004.

Please show a forumala that I could use to accomplish this for January
and then something harder like February (Harder because you will have
to filter out smaller months like Jan and the larger months)

If anyone could help, it would be greatly appreciated. I've already
tried numerous SUMPRODUCTS and AverageIfs and none seem to work...

Thanks in advance,

Kris Taylor
 
Hi
Kris
1. A non formula approach would be using a pivot table and grouping the
report by months

2. Formula approach:
=SUMPRODUCT(--(MONTH(A1:A1000)=1),--(YEAR(A1:A1000)=2004),B1:B1000)/SUM
PRODUCT(--(MONTH(A1:A1000)=1),--(YEAR(A1:A1000)=2004))
for January.

February:
=SUMPRODUCT(--(MONTH(A1:A1000)=2),--(YEAR(A1:A1000)=2004),B1:B1000)/SUM
PRODUCT(--(MONTH(A1:A1000)=2),--(YEAR(A1:A1000)=2004))
 
Or the somewhat shorter array formula

=AVERAGE(IF((MONTH(A1:A20)=1)*(YEAR(A1:A20)=2004),B1:B20))

entered with ctrl + shift & enter
 
Peo Sjoblom said:
Or the somewhat shorter array formula

=AVERAGE(IF((MONTH(A1:A20)=1)*(YEAR(A1:A20)=2004),B1:B20))

entered with ctrl + shift & enter

--

Regards,

Peo Sjoblom
Click to expand...

Thanks to the both of you! They both work great, although I am
currently using the Array. I now need to elaborate on this formula.
I know want to do the same thing but for multiple tabs within a
worksheet. For example, I have 4 tabs that I want to averageif for
each month. Columns are still A and B. I've tried simply increasing
the range with commas, however that does not seem to work.

Thanks for all of your help thus far! I look forward to seeing more
posts!

Thanks,

Kris Taylor
 
Back
Top