Formula to count cells that contain a number & are not shaded

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello,

I am trying to get Excel to count up blank (or non-shaded) cells, as well as
cells that contain a "1" in it. Can someone please suggest a formula to do
that? I really need it & an excel "guru" told me it could be done, but he
didn't remember how. Any help would be greatly appreciated.

Thank you!
 
If by "blank" you mean truly *empty* cells, *not* cells that equate to zero
length strings (""), and the "1" is a numerical 1, *not* a text "1", you can
try this *array* formula:

=SUM(--ISBLANK(A1:A10),COUNTIF(A1:A10,1))
 
If you want to count zero length strings ("") *and* truly blank cells and
the numerical 1, you could try this:

=SUM(COUNTIF(A1:A10,{"",1}))
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Count cell shading 4
Count if a cell has color shading 2
Excel Count Cells That Contain Text With "?" 0
using COUNTIF for cells that are coloured 1
Counting cells containing formulas with blank results 3
Can you regonise a coloured cell in a formula? 2
cell shading 4
linking shaded cells 1

Back
Top