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:

http://experiglot.wpengine.com/2011/07/13/integrating-multiple-if-conditions-in-excel/#comments

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.

Viswa
P.S.
I have tried the following, but these are NOT working:
=IF(AND($B$4:$B$139214<H$1,$C$4:$C$139214<$G4),FREQUENCY($B$4:$B$139214,H$1),0)

=IF($B$4:$B$139214<H$1,IF($C$4:$C$139214<$G4,FREQUENCY($B$4:$B$139214,H$1),0),0)

=IF($B$4:$B$23<H$1,IF($C$4:$C$23<$G10,FREQUENCY($B$4:$B$23,H$1),0),0)

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


***************************************************

Comments

Please Leave a Comment!





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