SUMPRODUCT vs COUNTIF

eyoung

New Member
Joined
Sep 25, 2006
Messages
3
Hello,
Great forums BTW!

I am trying to get SUMPRODUCT to return the number of cells that contain a date greater than 1/1/1970 in my array. The problem I seem to have is that every cell in my array contains a vlookup formula. SUMPRODUCT does not return the correct number of dates that are greater than 1/1/1970. COUNTIF does work but I need more than one condition in my total and SUMPRODUCT allows me multiple conditions.

Any thoughts?
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

Pauljj

Well-known Member
Joined
Mar 28, 2004
Messages
2,044
Not much help at this moment in time but on the soon to be released version of excel, you will have a new function called COUNTIFS which will do just want you want, I'm sorry I cannot answer your question in its current state
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
You didn't stipulate the full set of conditions and whether the lookup formulas you have return #N/A... That said:

=SUM(IF(ISNUMBER(DateRange),IF(DateRange > "1/1/1970"+0, 1)))

which must be confirmed with control+shift+enter.

The foregoing formula can be extended with conditionals and/or a range to sum.
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
Not much help at this moment in time but on the soon to be released version of excel, you will have a new function called COUNTIFS which will do just want you want, I'm sorry I cannot answer your question in its current state

I wonder why MS chose for that name...

MCOUNTIF instead of COUNTIFS and MSUMIF instead of SUMIFS look to me more apt...
 

eyoung

New Member
Joined
Sep 25, 2006
Messages
3

ADVERTISEMENT

Maybe a better discription will help : )

A|B|C
John|Active|2/2/2006
John|Active|3/4/2006
John|Active|""
John|Inactive|5/5/2006
Jake|Active|2/6/2006
Jake|Inactive|""
Jake|Active|3/31/2006
Jake|Inactive|6/26/2006

Column C contains a formula that will get the date for whoever is in Column A from an ODBC. As you can see line 3 has no date but the formula is there, the formula is written to not make an error visible but to leave the cell blank or "" if you will.
*note "" means nothing is visible unless cell is selected then you see the formula.

I am trying to create a summary page that will show me the number of several different conditions. Here is an example that works if I do not have formula's column C.

=SUMPRODUCT(--(A1:A8="John"),--(B1:B8="Active"),--(C1:C8>1/1/1970))

Any suggestions on a resolution?
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
Maybe a better discription will help : )

A|B|C
John|Active|2/2/2006
John|Active|3/4/2006
John|Active|""
John|Inactive|5/5/2006
Jake|Active|2/6/2006
Jake|Inactive|""
Jake|Active|3/31/2006
Jake|Inactive|6/26/2006

Column C contains a formula that will get the date for whoever is in Column A from an ODBC. As you can see line 3 has no date but the formula is there, the formula is written to not make an error visible but to leave the cell blank or "" if you will.
*note "" means nothing is visible unless cell is selected then you see the formula.

I am trying to create a summary page that will show me the number of several different conditions. Here is an example that works if I do not have formula's column C.

=SUMPRODUCT(--(A1:A8="John"),--(B1:B8="Active"),--(C1:C8>1/1/1970))

Any suggestions on a resolution?

Your trouble emanates from the fact that "" is greater than a number or a date...

If there are no #N/A's in the date range, try one of:

=SUMPRODUCT(--($A$1:$A$8="John"),--($B$1:$B$8="Active"),--ISNUMBER($C$1:$C$8))

=SUMPRODUCT(--($A$1:$A$8="John"),--($B$1:$B$8="Active"),--ISNUMBER($C$1:$C$8),--($C$1:$C$8 > "1/1/1970"+0))
 

eyoung

New Member
Joined
Sep 25, 2006
Messages
3
Aladin,
Thank you so much! After you posted that, I kicked my self and start to remember my college course. I tried it in a similar way but with my mind already being boggled I forgot to keep it simple : )

The below formula worked and I again thank you!

=SUMPRODUCT(--($A$1:$A$8="John"),--($B$1:$B$8="Active"),--ISNUMBER($C$1:$C$8),--($C$1:$C$8 > "1/1/1970"+0))
 

Forum statistics

Threads
1,141,227
Messages
5,705,141
Members
421,380
Latest member
Nuwan Sanjeewa Aponso

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top