SumIf AND

[NoodNutt]

Hi everyone

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

=SUMIF(Data!$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,"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.

 

[Fred Smith]

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

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

Regards,
Fred

 

[Jarek Kujawa]

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

 

[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.

 

[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

 

[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.

 

[Fred Smith]

Glad to help. Thanks for the feedback.

Fred

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.