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

[jimswinder]

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.

 

[Domenic]

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!

 

[jimswinder]

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)?

 

[Domenic]

Domenic:

Thanks that worked....

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)?

Try...

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

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

Hope this helps!

 

[jimswinder]

Thanks once again....that will do it.

You're welcome!


Try...

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

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

Hope this helps!

 

[jimswinder]

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.

 

[Domenic]

Is the formula returning an error message? If so, which one? Or are
you getting an incorrect result?

 

[jimswinder]

an incorrect result...it always comes up as "0"

Is the formula returning an error message? If so, which one? Or are
you getting an incorrect result?

 

[Domenic]

an incorrect result...it always comes up as "0"

Are you confirming the formula with CONTROL+SHIFT+ENTER?

 

[jimswinder]

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?

 

[Domenic]

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?

It indicates that you've entered the array formula correctly. For
additional information, see the help menu under 'array formula'.

 

[David Biddulph]

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?

It means it's an array formula.

 

[jimswinder]

How come the formula did not work until I did the CONTROL +SHIFT+ENTER??

 

[David Biddulph]

How come the formula did not work until I did the CONTROL +SHIFT+ENTER??

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

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