SumIf AND
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.
|