How do I count nonblank cells that meet criteria in another cell?

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

Guest

I need to add nonblank cells (cells have text) in a column that equal the
criteria of another cell. So I want to add the cells in Column C that have
text but also equal the date in Column A (which is equal to the date in Cell
A1).

I have tried the follwoing:
=SUM(IF(A2:A19=A1,IF(C3:C19="x",1,0)))
=COUNT(IF((A2:A19=A1),C2:C19))
=IF((A2:A19)=A1,COUNTIF(C2:C19,"x"))

Thanks for any help on this matter... I am sure it is an easy fix.
 
The ranges need to be the same size. Try...

=SUM(IF(A2:A19=A1,IF(C2:C19="x",1,0)))

....confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!
 
Domenic:

Thanks that worked....what do I need to replace "x" with in the formula if I
want it to find ANY text (non blank cells)?
 
jimswinder said:
Domenic:

Thanks that worked....
Click to expand...

You're welcome!
what do I need to replace "x" with in the formula if I
want it to find ANY text (non blank cells)?
Click to expand...

Try...

=SUM(IF(A2:A19=A1,IF(C2:C19<>"",1,0)))

....confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!
 
Thanks once again....that will do it.

Domenic said:
You're welcome!


Try...

=SUM(IF(A2:A19=A1,IF(C2:C19<>"",1,0)))

....confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!
Click to expand...
 
Well...it worked in my very simple test spreadsheet...but not my actual one
where I have several different worksheets....one where I am putting the
formula and another where it is looking for/at the data. I don't know how I
could explain or show you what I am actually wanting to do without sending
you the spreadsheet.
 
Is the formula returning an error message? If so, which one? Or are
you getting an incorrect result?
 
an incorrect result...it always comes up as "0"

Domenic said:
Is the formula returning an error message? If so, which one? Or are
you getting an incorrect result?
Click to expand...
 
Sorry...I didn't know about the CONTROL+SHIFT+ENTER.

When I do that, it doe now work, but it has put brackets { } around the
whole formula. What does that mean?
 
jimswinder said:
Sorry...I didn't know about the CONTROL+SHIFT+ENTER.

When I do that, it doe now work, but it has put brackets { } around the
whole formula. What does that mean?
Click to expand...

It indicates that you've entered the array formula correctly. For
additional information, see the help menu under 'array formula'.
 
Sorry...I didn't know about the CONTROL+SHIFT+ENTER.

When I do that, it doe now work, but it has put brackets { } around the
whole formula. What does that mean?
Click to expand...

It means it's an array formula.
 
How come the formula did not work until I did the CONTROL +SHIFT+ENTER??
Click to expand...

Because until then you didn't have an array formula.

As stated above:
"For additional information, see the help menu under 'array formula'."
 

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

countifs Function not giving desired result with date criteria 2
count nonblank cells 3
Count NonBlank Rows with Criteria in another column 5
count nonblank cells with conditions 2
SUMIF with criteria "<>" & "=" 4
How do I count # of cells with same value other column? 4
Counting cells in a selectable date range 0
Count if text meets given criteria 2

Back
Top