Counting with multiple criteria

I'm interested in populating a table that counts from the data set based on two values:
- the range that the predicted value falls into
- the actual value that occured

Please see attached for an example (attached as zip since it won't let me upload xls). In this example, I have manually counted the output in the "counting table", but would like this to be done automatically as it is for a large data set.

Any advice anyone can provide will be greatly appreciated! Thank you!

Anita

Anita

Try

A4: =SUMPRODUCT(--($A$3:$A$12>=$D4),--($A$3:$A$12<=$E4),--($B$3:$B$12=F$3))

Copy down / across as required.


rylo

Pjoaquin and Rylo,

Thank you VERY much!! I was trying to learn from some of the other posts on SUMPRODUCT and still haven't quite figured it out. I'll need to spend some time getting smart on it because it appears to be very useful.

Thank you thank you thank you.
Anita

One follow-up question:

Some of the "Actual" data fields on my larger sheet are empty since the actual hasn't occured yet. The current formula accounts the blank as an Actual of 0. Is it possible to have it not count it if the Actual is blank?

I thought about doing an IF statement, but I think that will only work on one cell, as opposed to the whole range.

Thanks again,
Anita

Perfect - Thanks Paul!