Help with Count IF Statement

[cstrange@gmail.com]

I am having trouble figuring out this formula. I need to count the
number of cells that have a value (or non-blank) if it meets a criteria
from a different row.

Database Worksheet
A B
1 brian 08/01/05
2 chris 08/15/05
3 pam
4 brian
5 brian 08/31/05


Totals/Formula Worksheet
F G
1 brian FORMULA WILL BE HERE
2 chris
3 pam

The formula should determine if A1:A5=brian, then it will count any
cells with a value ("paid" in this example) in row B. So in this
example, G1 should = 2 (i.e. brian had two cells where there was a date
value). Notice that there is one cell in B that related to "brian" but
does not have a value, so it is not included in the count.

Can anyone help me???

[Bob Phillips]

=sumproduct(--($A$1:$A$5=F1),--($B$1:$B$5<>""))

--

HTH

RP
(remove nothere from the email address if mailing direct)


<cstrange@gmail.com> wrote in message
news:1130884908.191252.278520@f14g2000cwb.googlegroups.com...
> I am having trouble figuring out this formula. I need to count the
> number of cells that have a value (or non-blank) if it meets a criteria
> from a different row.
>
> Database Worksheet
> A B
> 1 brian 08/01/05
> 2 chris 08/15/05
> 3 pam
> 4 brian
> 5 brian 08/31/05
>
>
> Totals/Formula Worksheet
> F G
> 1 brian FORMULA WILL BE HERE
> 2 chris
> 3 pam
>
> The formula should determine if A1:A5=brian, then it will count any
> cells with a value ("paid" in this example) in row B. So in this
> example, G1 should = 2 (i.e. brian had two cells where there was a date
> value). Notice that there is one cell in B that related to "brian" but
> does not have a value, so it is not included in the count.
>
> Can anyone help me???
>

[cstrange@gmail.com]

Thank you for your assistance. The formula above still does not appear
to work across multiple worksheets. For example, when I use exactly
what you have above in single worksheet, it works.

However, when I have the totals (the F & G columns above) on a
different worksheet, it keeps returning a "0".

Any ideas? The data worksheet has a lot more columns of data than my
example above, so I have all of my total caculations on a different
worksheet.

[Bob Phillips]

It should total on one worksheet the values on another worksheet. Are you
asking to total across worksheets rather than what I described here? If so,
what sheets are you referring to as your original email said nothing of
that.

--

HTH

RP
(remove nothere from the email address if mailing direct)


<cstrange@gmail.com> wrote in message
news:1130980579.575905.59610@g44g2000cwa.googlegroups.com...
> Thank you for your assistance. The formula above still does not appear
> to work across multiple worksheets. For example, when I use exactly
> what you have above in single worksheet, it works.
>
> However, when I have the totals (the F & G columns above) on a
> different worksheet, it keeps returning a "0".
>
> Any ideas? The data worksheet has a lot more columns of data than my
> example above, so I have all of my total caculations on a different
> worksheet.
>