Countif/sumproduct formula

[lorib01]

Hello,

Yesterday I got some great help with a problem (see http://www.mrexcel.com/forum/showthread.php?t=348357&goto=newpost) and I need some more today, please.

I have a sheet with repeated dates for several months and I need to break out data by week and then by certain criteria. I can do 1 or the other but combining the COUNTIF formula and the SUMPRODUCT formula has proven to be beyond me.

I have this now:

<code>=SUMPRODUCT(--(D2:D31719<="7/26/2008"+0),--(D2:D31719>"7/19/2008"+0),--(G2:G31719>"5"))</code>

but it returns a value of 0 which is incorrect.

What I need to do is have the formula return a sum of all of the fields in colG that are >5 within a date range. Once I find that # I have to divide it by another field and multiply by 100 to get the percent. It should be easy

I appreciate any help offered.

Thanks,

 

[schielrn]

I believe you need to get rid of the "" quotes around the 5 if there is actually a number in column G and not a text value number?

 

[lorib01]

Thanks for the quick reply but that's still returning a 0.

 

[theozz]

try

=SUMPRODUCT(--(D2:D31719<=date(2008,7,26)),--(D2:D31719>date(2008,7,19)),
--(G2:G31719>"5"))

=SUMPRODUCT(--(D2:D31719<=date(2008,7,26)),--(D2:D31719>date(2008,7,19)),
--(G2:G31719>5))

 

[schielrn]

What does this return:

=isnumber(D2)

or use any cell with a date in it for D2.

 

[lorib01]

=ISNUMBER(D2) returns true

The other formulas return 432 which makes me feel good until I do the division & multiplcation to get the %, then they return 16% when the actual result should be 28%. We're getting warmer, thanks for the help.

 

[schielrn]

Actually now that I look at it you want a sum, this would return a count:

=SUMPRODUCT(--(D2:D31719<="7/26/2008"+0),--(D2:D31719>"7/19/2008"+0),--(G2:G31719>5))

For the sum you would need:

=SUMPRODUCT(--(D2:D31719<="7/26/2008"+0),--(D2:D31719>"7/19/2008"+0),--(G2:G31719>5),G2:G31719)

But if the previous formula is returning 0 then you will not get a sum because it is counting nothing meeting ALL the criteria of:

1) A date in column D being >= 7/19/2008
2) A date in column D being <= 7/19/2008
3) A value in column G being > 5

Is there in fact data IN THE SAME ROW that matches all of these criteria?

 

[lorib01]

Yes, there is data >5 in the columns. When I separate out the weeks I run a COUNTIF formula which shows that 28% are >5. I checked on another week just to be sure and got similar incorrect results. It's very strange.

 

[schielrn]

Is there any way you can send me the spreadsheet? I am out of questions I can ask? You can send it here. Please remove any personal data that may be in there or change the values of them.

Thanks.

 

[hung2k]

I think that you should include the sum column as the last parameter
(Note that I removed the quotes around number 5)


=SUMPRODUCT(--(D2:D31719<="7/26/2008"+0),--(D2:D31719>"7/19/2008"+0),--(G2:G31719>5), (G2:G31719))

Hung2k