MrExcel Publishing
Your One Stop for Excel Tips & Solutions

count blank with multiple conditions


Posted by Kathi on May 11, 2001 4:20 AM

I need to count an empty cell in one column when it occurs in conjunction with 2 seperate conditions in two different columns. Example: Row A contains the potentially blank cell. I need to count that cell when "toys" occurs in the same row and in column B and also when "boys" occurs in the same row and column C. I hope I have made this clear. PLEASE help on this, as I am almost blind now from peering at my laptop screen in vain.
Thanks.


Posted by JAF on May 11, 2001 4:38 AM

You could do this with an Array Formula (search for "Multiple Conditions" and/or "Array" on search page, but these can slow down a large spreadsheet.

The "quick and dirty" method would be to have an extra column (say column D) which contains the following formula:
=IF(AND(A2="",B2="toys",C2="boys"),1,0)

Copy that formula down for all required rows and the sum of that column will be the number of instances where ALL 3 conditions are true - in other words where Column A is blank, Column B contains "toys" and Column C contains "boys".

Hope this helps.
JAF

Posted by Kathi on May 11, 2001 5:17 AM

It would probably be better if it were an array formula. I am working with two spreadsheets one of which (the second, where I maintain all the formulas) is static and the first is constantly being updated by data I import from a mdb. If I followed your suggetion here, it would mean entering the formula on the first spreadsheet which would mean constantly re-entering it. Any other suggestions?
Thanks again!


Posted by Aladin Akyurek on May 11, 2001 5:42 AM

Lets say you have data in A1:A100, B1:B100, and C1:C100

Put your 1st condition in D1 and the second in D2.

In D3 array-enter: =SUM((ISBLANK(A1:A100))*(B1:B100=D1)*(C1:C100=D2))


Note. In order to array-enter a formula, hit CONTROL+SHIFT+ENTER at the same time, instead of just ENTER.

Aladin

Posted by Kathi on May 11, 2001 5:58 AM


OH THANK YOU!! You have saved my LIFE!!!!!
Thanks, thanks, thanks!!!!