Sumif or Sumproduct 2 criterias not working
 

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

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

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

--
Bernard Liengme
www.stfx.ca/people/bliengme
remove CAPS in email address


"SMac" wrote in message
...
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

=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)


"SMac" wrote in message
...
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

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
SalesAid Software

"SMac" wrote in message
...
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

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

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

--
Don Guillett
SalesAid Software

"Don Guillett" wrote in message
...
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
SalesAid Software

"SMac" wrote in message
...
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