SUMPRODUCT vs COUNTIF

[eyoung]

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?

 

[Pauljj]

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]

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]

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]

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]

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]

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