Creating An Extra Column Is Often The Way To Go

Excel spreadsheets (.xls)

If you want to learn Excel, keep reading or Get 10 days of free unlimited access to Lynda.com. for professional help and Excel Tutorials
***************

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://www.experiglot.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.