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

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

--

HTH

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


wrote in message
oups.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???

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.

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)


wrote in message
ups.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.