Using SUMIF with dates

[yowzers]

Right now, I have a database in Sheet 1 by dates listed as 1/1/09, 2/15/09,
etc in column A and corresponding data in column B. Then I have a table on
Sheet 2 where column A is listed in text as Jan, Feb, Mar, etc. I would like
to put in a SUMIF function where I can get totals for column B on sheet 1 by
month IF the date in Sheet 1 column A equals the month listed in the column A
in sheet 2. Right now, the only way I can do this is by creating a column C
in sheet 1 and having the date in column A turned into a month by using
=TEXT(A1,"mmm"). Then in sheet 2, I use the SUMIF function where it sums if
column C in sheet 1 equals column A in sheet 2. Is there anyway I can put
this all into one formula so I don't have to have that column C in sheet 1?

 

[Peo Sjoblom]

This would be easier to use


=SUMPRODUCT(--(TEXT(Sheet1!$A$1:$A$150,"mmm")=A1),Sheet1!$B$1:$B$150)

change the cell references to fit your data then copy down





--


Regards,


Peo Sjoblom

 

[yowzers]

Awesome, this works great. How about for AVERAGEIF function for the same
criteria?

 

[T. Valko]

Try this array formula** :

=AVERAGE(IF(TEXT(Sheet1!$A$1:$A$150,"mmm")=A1,Sheet1!$B$1:$B$150))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

 

[yowzers]

One more question. How about if I want another criteria? In the example
above, I want it to sort by Month, but how about if i want to sort by Month
and Year? I tried this formula and it doesnt seem to work.

=SUMPRODUCT(--(TEXT(Sheet1!$A:$A,"mmm")=A1),--(YEAR(Sheet!$A:$A)=B1),Sheet1!$B:$B)

 

[T. Valko]

I tried this formula and it doesnt seem to work.

=SUMPRODUCT(--(TEXT(Sheet1!$A:$A,"mmm")=A1),--(YEAR(Sheet!$A:$A)=B1),Sheet1!$B:$B)

Specifically, what does "doesnt seem to work" mean?

One thing, unless you're using Excel 2007 you can't use entire columns as
range references.

What's in A1?
What's in B1?

Are there any TEXT entries in Sheet!$A:$A? If so, the YEAR function will
return an error.

 

[yowzers]

By not working, I mean I am getting an error message.

I am using Excel 2007.

Sheet1!$A:$A is full of dates in the DATE format. A1 would say "Jan" in
text and B1 would say "2009" in text. Basically I want a sum if the dates in
column A:A are Jan AND 2009.

Thanks for your help.

 

[T. Valko]

Try this...

With Sheet1!A:A containing true Excel dates...
A1 = the month name as a TEXT entry = Jan
B1 = the year number = 2009

=SUMPRODUCT(--(TEXT(Sheet1!$A:$A,"mmmyyyy")=A1&B1),Sheet1!$B:$B)

 

[David Biddulph]

And you'd like us to guess what the error message is? :-(

 

[yowzers]

Perfect!

Try this...

With Sheet1!A:A containing true Excel dates...
A1 = the month name as a TEXT entry = Jan
B1 = the year number = 2009

=SUMPRODUCT(--(TEXT(Sheet1!$A:$A,"mmmyyyy")=A1&B1),Sheet1!$B:$B)

--
Biff
Microsoft Excel MVP





.

 

[yowzers]

Last question, I promise.....maybe =P

I tried doing this same thing with the AVERAGEIF function and the answer is
coming back 0 for some reason. Am I doing something wrong?

 

[yowzers]

I figured out the answer to the question below on my own. Now I need to add
a >0 function to both the SUMIF and AVERAGEIF functions as well as the MONTH
and YEAR

 

[T. Valko]

I am using Excel 2007.

Now I need to add a >0 function

=SUMPRODUCT(--(TEXT(Sheet1!$A:$A,"mmmyyyy")=A1&B1),--(Sheet1!$B:$B>0),Sheet1!$B:$B)

Array entered:

=AVERAGE(IF(TEXT(Sheet1!$A:$A,"mmmyyyy")=A1&B1,IF(Sheet1!$B:$B>0,Sheet1!$B:$B)))

Even though you're using Excel 2007 you should still use as small a range as
possible. Both of those formulas will evaluate *EVERY* cell in the
referenced columns.