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

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!

In article <[email protected]>,
jimswinder <[email protected]> wrote:

> 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:

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" wrote:

> 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.

In article <[email protected]>,
jimswinder <[email protected]> wrote:

> 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!

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

"Domenic" wrote:

> In article <[email protected]>,
> jimswinder <[email protected]> wrote:
>
> > 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!
>

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" wrote:

> In article <[email protected]>,
> jimswinder <[email protected]> wrote:
>
> > 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!
>

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

In article <[email protected]>,
jimswinder <[email protected]> wrote:

> 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.

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

"Domenic" wrote:

> Is the formula returning an error message? If so, which one? Or are
> you getting an incorrect result?
>
> In article <[email protected]>,
> jimswinder <[email protected]> wrote:
>
> > 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.
>

In article <[email protected]>,
jimswinder <[email protected]> wrote:

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

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

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" wrote:

> In article <[email protected]>,
> jimswinder <[email protected]> wrote:
>
> > an incorrect result...it always comes up as "0"
>
> Are you confirming the formula with CONTROL+SHIFT+ENTER?
>

In article <[email protected]>,
jimswinder <[email protected]> wrote:

> 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'.

In article <[email protected]>,
jimswinder <[email protected]> wrote:

> 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'.

"jimswinder" <[email protected]> wrote in message
news:[email protected]...

> "Domenic" wrote:
>
>> In article <[email protected]>,
>> jimswinder <[email protected]> wrote:
>>
>> > an incorrect result...it always comes up as "0"

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

> 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.
--
David Biddulph

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

"Domenic" wrote:

> In article <[email protected]>,
> jimswinder <[email protected]> wrote:
>
> > 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'.
>

"jimswinder" <[email protected]> wrote in message
news:[email protected]...

> "Domenic" wrote:
>
>> In article <[email protected]>,
>> jimswinder <[email protected]> wrote:
>>
>> > 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'.

> 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'."
--
David Biddulph