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

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
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
 
Upvote 0
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.
 
Upvote 0
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...
 
Upvote 0
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?
 
Upvote 0
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))
 
Upvote 0
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))
 
Upvote 0

Forum statistics

Threads
1,217,357
Messages
6,136,085
Members
449,990
Latest member
orthodmd

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
Back
Top