For every row with cells in col A that aren't blank, count how many blank cells in col B

[supersonicsaint]

Hi everyone

I appreciate this will probably be quite a simple solution but for the life of me I can't work it out

I have a big spreadsheet with a huge list of invoice numbers in col A, then spread across loads of other columns is various details related to said invoices

I want to have, in one row at the top of the spreadsheet, for every column, a quick check to do the following...

Check that for every row in which there is an invoice number in col A (i.e. <>""), check how many cells in col B are blank

The reason being, the spreadsheet is pulling in values from another spreadsheet and isn't protected so there's a risk that cell values could be accidentally deleted which could skew our data

The reason I want to only check for blank values in rows in which there is an invoice number in column A is that some rows (such as to break up months) will be blank so I don't care if the value in col B on that row is blank

Hope that makes sense!

Many thanks
Joe

[Tony Valko]

Do you mean something like this...

Data Range

	A	B	C
4	Blanks	4	7
5	Invoice #	Data	Data
6	1	x
7	2	x
8	3		x
9	4		x
10	5	x
11	6
12	7	x
13	8		x
14	9	x
15	10	x
16	------	------	------

This formula entered in B4 and copied across:

=COUNTIFS($A$6:$A$15,"<>",B6:B15,"")

[supersonicsaint]

Hi Tony

Thanks very much that's a great start

Now how could I go about amending this so that it only returns a count of cells that are completely blank i.e. do not contain any formulas?

Joe

[Tony Valko]

Does that mean some cells contain formulas that return formula blanks "" and some cells are empty (do not contain formulas) ?

So, which of these types of cells do you want to count?

[supersonicsaint]

Yes some will contain formulas which return blanks - such as "date paid" will be blank if the invoice is unpaid

There is a possibility some might have the formulas deleted altogether due to human error

Because the first instance is to be expected I'm not interested in a count of these (I'm sorry I wasn't clear in my original message)

Because there are thousands of rows of invoices and I cannot run the risk of deleted formulas in cells, I want one row at the top with a check in each column counting up any such instances

Cheers

[Tony Valko]

This will count EMPTY cells...

=SUMPRODUCT(--ISBLANK(B6:B15))

[supersonicsaint]

Thank you but how do I tie this into the original formula?

That will count blank cells in the column B but I need to only count blank cells in column B if there is an invoice number in that row on column A

As I say I expect some to be blank because there aren't necessary invoices in every row such as dividers in between periods

Cheers
Joe

[Tony Valko]

There's a difference between "blank" and empty!

=SUMPRODUCT(--(A6:A15<>""),--ISBLANK(B6:B15))

[supersonicsaint]

That's the one!

Thanks for all the help

[Tony Valko]

You're welcome. Thanks for the feedback!