Forum Discussion
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't follow any type of pattern, but are instead determined by the size of the facility we're looking at. The first facility spans from H4:H27, with H27 being the cell that contains the subtotal formula for that entire facility) while the second facility spans from H28:H32, with H32 containing the subtotal formula for the second facility, and so on for the remaining cells until we get to H428. Now, not every cell contains data. What I've been tasked with doing is finding the number of cells that contain data, whilst excluding the subtotal cells from that count. Now, I've tried using the COUNTA formula as follows =COUNTA(H4:H428) but I'm not sure how to exclude those cells that contain the subtotal formula from this number. I know there must be a way to do this without having to manually subtract each specific subtotal cell from the COUNTA formula, but can't find a way to do this. Can anyone help with this?
Thanks
=SUMPRODUCT((NOT(ISFORMULA(H4:H26)))*(H4:H26<>""))You can try SUMPRODUCT to count the cells without a formula and excluding blank cells.
=SUMPRODUCT((NOT(ISFORMULA(H4:H26)))*(H4:H26<>""))You can try SUMPRODUCT to count the cells without a formula and excluding blank cells.
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?
=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.
