COUNTA function across multiple ranges
Solved
Lise761
Posted messages
2
Registration date
Status
Member
Last intervention
-
Vaucluse Posted messages 27336 Registration date Status Contributor Last intervention -
Vaucluse Posted messages 27336 Registration date Status Contributor Last intervention -
Hello,
I am looking for a formula in Excel to calculate the number of empty cells across multiple ranges.
The function COUNTBLANK only works on a single range.
Basically, I have a table with 3 columns: one with titles, a second called "quote," and a third called "order."
I enter the letter "O" when a quote or an order is sent.
I would like to know the number of "titles" that have neither placed an order nor requested a quote.
I hope I was clear enough!
Thank you.
I am looking for a formula in Excel to calculate the number of empty cells across multiple ranges.
The function COUNTBLANK only works on a single range.
Basically, I have a table with 3 columns: one with titles, a second called "quote," and a third called "order."
I enter the letter "O" when a quote or an order is sent.
I would like to know the number of "titles" that have neither placed an order nor requested a quote.
I hope I was clear enough!
Thank you.
3 answers
-
Good evening,
Maybe like this
=COUNTA(A1:E30)
or if the ranges are not contiguous
=COUNTA(A1:A20,D1:D19,G1:G19)
--
Best regards,
Mike-31
A period of failure is a perfect time to sow the seeds of knowledge. -
Hello
If the ranges are separated, you can add the countblank()
=COUNTBLANK(B7:B9)+COUNTBLANK(F7:F10)
If the ranges have the same number of rows
=SUMPRODUCT((B7:B14="")+(F7:F14=""))
otherwise maybe a macro?
Best regards -
The first one (Mike_31) doesn't work (I've already tried it).
The second one (ccm81) adds up the number of empty cells in both ranges, which is not what I'm looking for!
The third one (Vaucluse) works!! Hallelujah!!
Thank you very much!
