Forum Discussion

Copper Contributor

Apr 24, 2023

Solved

Using a Count formula but excluding cells containing a formula

I've been asked to figure out a small problem at work with some Excel sheets for my coworker. Essentially, I'm looking at Column H that spans from H4 to H428. They're broken up into sections that don...

Formulas and Functions

OliverScheurich
Apr 24, 2023
Cannonball285
=SUMPRODUCT((NOT(ISFORMULA(H4:H26)))*(H4:H26<>""))
You can try SUMPRODUCT to count the cells without a formula and excluding blank cells.

OliverScheurich

Gold Contributor

Apr 24, 2023

=SUMPRODUCT((NOT(ISFORMULA(H4:H26)))*(H4:H26<>""))

You can try SUMPRODUCT to count the cells without a formula and excluding blank cells.

Cannonball285
Copper Contributor
Apr 24, 2023
Okay great it looks like this one worked, thank you. Could you explain why exactly this worked though? Maybe a breakdown of the parts of the formula? I have pretty baseline knowledge of Excel and haven't explored much with formulas yet. edit: I think I understand the first half of the formula. The first half is stating that it's going to count the cells that don't contain a formula, but what does that second half mean? I know that H4:H428 would be my range, but what does the <>"" mean?
- OliverScheurich
  Gold Contributor
  Apr 24, 2023
  Cannonball285
  =SUMPRODUCT((NOT(ISFORMULA(H4:H26)))*NOT(ISBLANK(H4:H26)))
  <>"" means unequal blank. The above formula better explains what the second half does.
  
  You can check the result for each cell when you select the array as shown in the screenshot. Then press F9 for the next screenshot.
  
  In the formula bar there are 1s and 0s. 1 for all cells that aren't empty and aren't formulas. 0 for all cells that are empty or are formulas. SUMPRODUCT adds up the 1s and 0s.
  
  In order to excape this view you can click in the cross highlighted in the screenshot or press ctrl+Z.

Resources