Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
=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 |
#5
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to use SUMIF to return sums between two values located in cells | Excel Worksheet Functions | |||
Sumif not Sumproduct | Excel Worksheet Functions | |||
Sumproduct function not working | Excel Worksheet Functions | |||
adding two sumproduct formulas together | Excel Worksheet Functions | |||
SUMIF or SUMPRODUCT or something else? | Excel Worksheet Functions |