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?
 

Some videos you may like

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

Pauljj

Well-known Member
Joined
Mar 28, 2004
Messages
2,005
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,192
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,192
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,192
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))
 

Watch MrExcel Video

Forum statistics

Threads
1,109,001
Messages
5,526,203
Members
409,686
Latest member
Tori83

This Week's Hot Topics

Top