SumIf AND

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:O0)

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

TIA
Mark.

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

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

Regards,
Fred

"NoodNutt" wrote in message
...
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:O0)

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

TIA
Mark.

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



On 4 Maj, 13:11, "NoodNutt" wrote:
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:O0)

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

TIA
Mark.

Thx Fred & Jarek

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

=SUMPRODUCT(--(Data!$O2:$O2000),--(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$10000)*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:$O2000))

=SUMPRODUCT((Data!$P2:$P200="On Time")*(Data!$O2:$O2000))


Where-as this equates to 0.

=SUMPRODUCT(Data!$P2:$P200="On Time")*(Data!$O2:$O2000)




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.

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" wrote in message
...
Thx Fred & Jarek

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

=SUMPRODUCT(--(Data!$O2:$O2000),--(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$10000)*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:$O2000))

=SUMPRODUCT((Data!$P2:$P200="On Time")*(Data!$O2:$O2000))


Where-as this equates to 0.

=SUMPRODUCT(Data!$P2:$P200="On Time")*(Data!$O2:$O2000)




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.

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" wrote in message
...
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" wrote in message
...
Thx Fred & Jarek

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

=SUMPRODUCT(--(Data!$O2:$O2000),--(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$10000)*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:$O2000))

=SUMPRODUCT((Data!$P2:$P200="On Time")*(Data!$O2:$O2000))


Where-as this equates to 0.

=SUMPRODUCT(Data!$P2:$P200="On Time")*(Data!$O2:$O2000)




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.

Glad to help. Thanks for the feedback.

Fred

"Noodnutt @ Work" wrote in message
...
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" wrote in message
...
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" wrote in message
...
Thx Fred & Jarek

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

=SUMPRODUCT(--(Data!$O2:$O2000),--(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$10000)*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:$O2000))

=SUMPRODUCT((Data!$P2:$P200="On Time")*(Data!$O2:$O2000))


Where-as this equates to 0.

=SUMPRODUCT(Data!$P2:$P200="On Time")*(Data!$O2:$O2000)




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.