Sumif or Sumproduct 2 criterias not working

[Guest]

I used the following formula:
=SUMPRODUCT(--('PO LIST'!H3:H88="Calibration"),--('PO
LIST'!E3:E88="Jan"),('PO LIST'!C3:C88))

The first part looks for "Calibration", then I need it to look for the month
"Jan" and when it finds those two to calculate C3:C88.

I think my issue is with the field "MONTH" that contains a formated versus
("Jan"), off the column next to it with "DATE" i.e. 01/05/04 - is it not
recoginizning "Jan" as a word or does it still look at this field as a date,
if so how do I enter criteria.

Your help is much appreciated!!

Thanks, Stacey

 

[Dave O]

Hi, Stacey-
I suspect Excel is still looking at column E as if it is a date. You
can use the MONTH() function, which returns a number from 1 to 12,
against a date. Your formula would look like this:
=SUMPRODUCT(--('PO LIST'!H3:H88="Calibration"),--(Month('PO
LIST'!E3:E88)=1),('PO LIST'!C3:C88))

Let us know if this works!

Dave O

 

[Bernard Liengme]

If the E stuff is really dates try --(MONTH('PO LIST"!E2:E88)=1)

 

[Bob Phillips]

=SUMPRODUCT(--('PO LIST'!H3:H88="Calibration"),--(TEXT('PO
LIST'!E3:E88,"mmm")="Jan"),('PO LIST'!C3:C88))

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Don Guillett]

Forget about the column named Jan or use if if desired since it is just a
format of the date and therefore the same. so us

=SUMPRODUCT(--('PO LIST'!H3:H88="Calibration"),--(month('PO
LIST'!E3:E88)="Jan"),('PO LIST'!C3:C88))

 

[Don Guillett]

that should have been =1 instead of ="Jan"

=SUMPRODUCT(--('PO LIST'!H3:H88="Calibration"),--(month('PO