Help with Count IF Statement

[cstrange]

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]

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)