SumIf AND

N

NoodNutt

Hi everyone

I use the SumIF() usually to great effect, but this one has the grey matter
throbbing.

=SUMIF(Data!$P:p,"On Time",Data!O:O)

This returns the expected total if I wanted all the amounts in Column "O",
but in this case, I don't, I only need the sum of "O", if the figure in "O"
is >0.

I use SumProduct() which works great for counting the number of cells that
match my set conditions, but it does not sum the actual total value of said
cells within the required conditions.

Essentially, I am looking for something like:

=SUMIF(AND(Data!$P:p,"On Time",Data!O:O)Data!O:O>0)

I need to identify and sum seperately, those totals above and below zero for
mapping specific data.

TIA
Mark.
 
F

Fred Smith

If you have XL2007, use Sumifs. If not, use Sumproduct, as in:
=Sumproduct((Data!P1:p1000="On Time")*(Data!O1:O1000>0))

Adjust the range to suit. You can't use full columns.

Regards,
Fred
 
N

NoodNutt

Thx Fred & Jarek

This is the formula I use to give me a "Cell Count"

=SUMPRODUCT(--(Data!$O2:$O200>0),--(Data!$P2:$P200="On Time"))

It returns the correct "On Time" count.


Jarek, your example returns a " #VALUE " Cell response.

=SUMPRODUCT((Data!$P$1:$P$1000="On
time")*(Data!$O$1:$O$1000>0)*Data!$O$1:$O$1000)


All of the following formula's still equate to 14 (Cell Count), not the
overall value (Cell Sum) itself.

=SUMPRODUCT(--(Data!$P2:$P200="On Time")*(Data!$O2:$O200>0))

=SUMPRODUCT((Data!$P2:$P200="On Time")*(Data!$O2:$O200>0))


Where-as this equates to 0.

=SUMPRODUCT(Data!$P2:$P200="On Time")*(Data!$O2:$O200>0)




In one of my cells I calc an overall total.

=SUMIF(Data!O:O,">0")

Works a treat to get the overall, but it is from this overall, I need to
break it down into 3 categories, On Time, Early & Late.

The opposite is also needed, I also need to identify the above 3 with
negative 0 amounts for a seperate rebating purpose.

Thx again

Mark.
 
F

Fred Smith

Jarek's formula should work. I would check again to ensure you entered it
correctly, and that there are no errors in your data.

Regards
Fred
 
N

Noodnutt @ Work

And once again, D'oh.....

Thx heaps to both, you were correct Fred, I neglected to adjust the range
which had the title row included, hence the #Value.

All is good once again...

Cheers & Thx

Regards
Mark.
 
F

Fred Smith

Glad to help. Thanks for the feedback.

Fred

Noodnutt @ Work said:
And once again, D'oh.....

Thx heaps to both, you were correct Fred, I neglected to adjust the range
which had the title row included, hence the #Value.

All is good once again...

Cheers & Thx

Regards
Mark.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

SUMIF with criteria "<>" & "=" 4
SumIf equation question 6
sumif issues 1
Sumif using partial string 7
complex CountIf situation 3
Sumif weekday 3
Can I have two conditions in a SUMIF? 5
sumif formula 2

Top