SUM(IF...(IF...(IF.... question

[Prodiclson]

I am trying to sum a column based on multiple criteria... specifically a column of time (hh:mm:ss) IF the value is greater than 00:30:00 + IF the value in another column (same row) matches a specific text value in another cell + IF the value in another column matches the date... (I hope I explained this OK...??)

I am having some troubles doing this and wa hoping for a little help from some fellow tipsters....

 

[barry houdini]

I am not looking for a product (multiplication) but a simple sum with multiple conditions (I am not thoughly familiar with the SUMPRODUCT function, so maybe I am off base here)

SUMPRODUCT does multiply and then sum the products but it's simply a means to calculate a sum with multiple conditions. If you get zero then that means that either none of your conditions match...or perhaps your times are text formatted

Does it make any difference if you use this formula?

=SUMPRODUCT(('Data'!D5:D379+0>"0:30"+0)*('Data'!H5:H379=B$1)*('Data'!B5:B379=$A2),'Data'!D5:D379+0)

If that doesn't help then try testing each of the criteria, e.g individually with COUNTIF

=COUNTIF('Data'!D5:D379,">0:30")
=COUNTIF('Data'!H5:H379,B$1)
=COUNTIF('Data'!B5:B379,$A2)

 

[Prodiclson]

I will try both the +0 concept from Mr Houdini and also the 'helper' column.... I had not thought of the helper column before...

I will keep everyone posted to my progress, or lack there of...

Thanks for all the great help so far Gentlemen and Ladies

 

[SmokeyVol]

I know this formula doesn't work (or I got an error that I don't see) but what is the correct solution?

=countif(master!b2:160=1,h2:h160="SOLD")

I'm trying to count the number of times "1" is shown in column "B" only when column "H" has the value "SOLD" in it within the same row......

 

[schielrn]

Try:

=sumproduct((master!b2:160=1)*(h2:h160="SOLD"))

Hope that helps.

 

[SmokeyVol]

I know this formula doesn't work (or I got an error that I don't see) but what is the correct solution?

=countif(master!b2:160=1,h2:h160="SOLD")

I'm trying to count the number of times "1" is shown in column "B" only when column "H" has the value "SOLD" in it within the same row......

I had an error within my error
***=countif(master!b2:160=1,master!h2:h160="SOLD")

Try:

=sumproduct((master!b2:160=1)*(h2:h160="SOLD"))

Hope that helps.

No luck

 

[spg2112]

You can also try using an array formula. After you have your formula written, and while you're still in the formula edit bar, hit cntrl+shift+enter. this will enclose the formula in {} and sum on multiple criteria.

={sum(if(A=X,if(B=Y,if(C=Z,SUMRANGE))))}

Steve

 

[schielrn]

So this doesn't work:

=sumproduct((master!b2:160=1)*(master!h2:h160="SOLD"))

Is it maybe a text number 1?

=sumproduct((master!b2:160="1")*(master!h2:h160="SOLD"))

 

[SmokeyVol]

So this doesn't work:

=sumproduct((master!b2:160=1)*(master!h2:h160="SOLD"))

Is it maybe a text number 1?

=sumproduct((master!b2:160="1")*(master!h2:h160="SOLD"))

Nevermind the first one did work..... I had the wrong column for "SOLD"

Thanks for the help!