Creating An Extra Column Is Often The Way To Go

Excel spreadsheets (.xls)

Obviously, as most of you can imagine, I do get a lot of different excel related questions and I do try to answer to as many of them as I can. Here is one that was posted in a comment at:

Please help. I have a very similar problem, but am not able to resolve it with COUNTIF, FREQUENCY, etc.

My problem is to count ALL such events when:
1) The value under Column-A is less than 0.5, the value under Column-B is less than 11.25;
2) Similarly, number of events when the value under Column-A is less than 1.5, and the value under Column-B is less than 11.25; and so on.

I have tried the following, but these are NOT working:



I rarely get this exact question but I do get several requests that can be resolved in the same way. Here is how I would resolve this. First, let’s take a look at a sample of data:

I understand that it would be very easy to have the entire condition verified through one simple formula. I don’t think it’s possible in this specific case. Perhaps it is but if there is no problem with adding a column (even if it means hiding it, putting it in another sheet, etc), then it will be much easier that way. Here is how it would look like:

Then, I will simply add “if” conditions to verify the arguments and count the number. I will add conditions one at a time so to start off:

=IF(A7<$C$5,1,0) Basically, if it fulfills the first argument I get “1”. If not, 0. Then, I simply want to make sure that if it fills criteria #1, it will also do the same for #2, so: =IF(A7<$C$5,IF(B7<$C$6,1,0),0) You see the new look here:

Then, I will simply sum the number of events here:

Easy enough right?

You can download the spreadsheet here


Look Good at Work and Become Indispensable Become an Excel Pro and Impress Your Boss



Please Leave a Comment!

Please note: Comments may be moderated. It may take a while for them to show on the page.