Match
 

In coloumn A some cells contain "Y" coloumn F also has cells that contain a
"Y" i would like to know how to sum which same rows in A and F contain "Y"

Match
 

Zhead wrote:
In coloumn A some cells contain "Y" coloumn F also has cells that contain a
"Y" i would like to know how to sum which same rows in A and F contain "Y"

Try:

=SUMPRODUCT(--(A:A="Y"),--(F:F="Y"))

Match
 

"smartin" wrote:
Zhead wrote:
In coloumn A some cells contain "Y" coloumn F also has cells that contain
a "Y" i would like to know how to sum which same rows in A and F contain
"Y"

Try:
=SUMPRODUCT(--(A:A="Y"),--(F:F="Y"))

I don't believe SUMPRODUCT permits column references like that, as least not
in Excel 2003.


Zhead wrote:
i would like to know how to sum which same rows in A and F contain "Y"

If by "sum", you mean "count", then try:

=SUMPRODUCT((A1:A100="y")*(F1:F100="y"))

But if you mean that you would like to sum another range (X1:X100)
corresponding to those conditions, try:

=SUMPRODUCT((A1:A100="y")*(F1:F100="y"),X1:X100)

Match
 

JoeU2004 wrote:

"smartin" wrote:
Zhead wrote:
In coloumn A some cells contain "Y" coloumn F also has cells that
contain a "Y" i would like to know how to sum which same rows in A
and F contain "Y"

Try:
=SUMPRODUCT(--(A:A="Y"),--(F:F="Y"))

I don't believe SUMPRODUCT permits column references like that, as least
not in Excel 2003.


Zhead wrote:
i would like to know how to sum which same rows in A and F contain "Y"

If by "sum", you mean "count", then try:

=SUMPRODUCT((A1:A100="y")*(F1:F100="y"))

But if you mean that you would like to sum another range (X1:X100)
corresponding to those conditions, try:

=SUMPRODUCT((A1:A100="y")*(F1:F100="y"),X1:X100)

You are correct. SUMPRODUCT cannot handle references to full columns in
E2003, (which I knew all along, but cavalierly overlooked by posting
without testing). Thanks for catching my error!

Match
 

Thanks for the information.
I am using this formula and it does what I need.
=IF(--(A:A="Y"),(--(F:F="Y")))

"smartin" wrote:

JoeU2004 wrote:

"smartin" wrote:
Zhead wrote:
In coloumn A some cells contain "Y" coloumn F also has cells that
contain a "Y" i would like to know how to sum which same rows in A
and F contain "Y"

Try:
=SUMPRODUCT(--(A:A="Y"),--(F:F="Y"))

I don't believe SUMPRODUCT permits column references like that, as least
not in Excel 2003.


Zhead wrote:
i would like to know how to sum which same rows in A and F contain "Y"

If by "sum", you mean "count", then try:

=SUMPRODUCT((A1:A100="y")*(F1:F100="y"))

But if you mean that you would like to sum another range (X1:X100)
corresponding to those conditions, try:

=SUMPRODUCT((A1:A100="y")*(F1:F100="y"),X1:X100)

You are correct. SUMPRODUCT cannot handle references to full columns in
E2003, (which I knew all along, but cavalierly overlooked by posting
without testing). Thanks for catching my error!