Hope the title makes sense, I need to pick up blank cells where the same row in another column is not blank.
For example the table below should result in 1 - as there is only 1 row that matches the criteria of have col 1 being not blank and col 2 being blank.
SA1 SA2 2 1
I was thinking a sumproduct might help but to no avail...
Also tried to no avail...
=SUMPRODUCT(--(A7:A56<>""),--(COUNTBLANK(B7:B56)))
=COUNTIFS(A7:A56,"counta",B7:B56,"countblank")
Last edited by stephen_; 06-21-2012 at 07:09 AM. Reason: add another formula
Hi and welcome to the forum
One way is this.
=SUMPRODUCT((A7:A56<>"")*(B7:B56=""))
Regards
Fotis.
-This is my Greek whisper to Europe.
--Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.
Advanced Excel Techniques: http://excelxor.com/
--KISS(Keep it simple Stupid)
--Bring them back.
---See about Acropolis of Athens.
--Visit Greece.
Fotis - many thanks for the help and welcome! (I've added to your reputation).
Glad to see I was'nt a million miles off with the SUMPRODUCT, just making it too complicated!
PS Is there any need to add {}? (always get confused with these)...
You are welcome.
Thank you for your intention to add in my reputation(I don't see your name in my list), but this is not the important thing. Important is that you find your solution.
Yes you had the correct logic.
No you don't need {} in SUMPRODUCT. In fact IS an Array formula, but does not need {}.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks