CODEWITHSUNDEEP
excel
Brownell3
Brownell3

Reputation: 3

Excel 2010: Counting cells with adjacent cell blank

I have data in rows where each column represents a day, some of which are blank and some of which have numbers. I want to "scan" down the row, comparing each cell with the one before to the left of it (or the one 2 spaces left of it, etc). For example, I want to sum the number of cells (days) with a blank in the cell before it. Or, I want to sum the number of cells that are greater than the cell to the left of it. I can't figure out how to dynamically compare to the prior column using addresses that change with each cell.

Upvotes: 0

Views: 14796

Answers (2)

manimatters
manimatters

Reputation: 434

This is from what I understood from your Question:

If you refer to the screenshot below, you want to count Columns B to H if the column has a number and the column to the left is blank.

So, if this was just one column, we would write the formula as:

=COUNTIFS(B3,">0",A3,"")

Now since you want to do this for a range of columns (an array of cells), you need to do something like:

=COUNTIFS(B3:H3,">0",A3:G3,"")

and accept with a Ctrl + Shift + Enter. So the formula would be displayed as:

{=COUNTIFS(B3:H3,">0",A3:G3,"")}

enter image description here

Upvotes: 3

ApplePie
ApplePie

Reputation: 8942

Array formulas are perfect for this task. I can't give you a lecture on the topic but you should read on it. Basically, you can select multiple cells in a range and act as if it was one cell.

For example, you could do something like this:

=SUM(IF($A$1:$A$100="", 1, 0))

When you enter array formulas, be sure to hit CTRL + SHIFT + ENTER in the formula box to signify that you want Excel to treat this formula as an array formula. Otherwise you will not get the expected results.

Using this method you can do any kind of comparison. Sometimes it helps to see how Excel treats the formula. You can select part of the formula in the formula editor and hit F9 to see what this segment computes to according to Excel.

Upvotes: 0

Related Questions