calculate total number of items that meet 2 over multiple sheets
 

I have several sheets setup from which I need to caluculate: how many of the
cells in a range meet both criteria. It's setup something like the below:

A B C D E F....
1 y y n n y y
2 n y n y y n

I need to determine how many "y" in range A1:F1 that are also "y" in A2:F2
across multiple sheets.

Thanks!

calculate total number of items that meet 2 over multiple sheets
 

Give this a try...

=SUMPRODUCT(--(A1:F1&A2:F2="yy"))

Rick


"twototango" wrote in message
...
I have several sheets setup from which I need to caluculate: how many of
the
cells in a range meet both criteria. It's setup something like the below:

A B C D E F....
1 y y n n y y
2 n y n y y n

I need to determine how many "y" in range A1:F1 that are also "y" in A2:F2
across multiple sheets.

Thanks!

calculate total number of items that meet 2 over multiple sheets
 

More than 10% quicker

=SUMPRODUCT(--($A$1:$F$1="y"),--($A$2:$F$2="y"))

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Rick Rothstein (MVP - VB)" wrote in
message ...
Give this a try...

=SUMPRODUCT(--(A1:F1&A2:F2="yy"))

Rick


"twototango" wrote in message
...
I have several sheets setup from which I need to caluculate: how many of
the
cells in a range meet both criteria. It's setup something like the
below:

A B C D E F....
1 y y n n y y
2 n y n y y n

I need to determine how many "y" in range A1:F1 that are also "y" in
A2:F2
across multiple sheets.

Thanks!

calculate total number of items that meet 2 over multiple sheets
 

Interesting... I would not have expected the time difference to be that
great. I guess it is the concatenation that slows it down. I would expect,
being a situational counting operation, that this SUMPRODUCT will probably
appear only one time (as opposed to being copied down), so my expectation is
that the time difference would not be significant. Out of curiosity, does
using the absolute references add anything to the time savings (that is, is
relative referencing slower than absolute referencing)?

Rick


"Bob Phillips" wrote in message
...
More than 10% quicker

=SUMPRODUCT(--($A$1:$F$1="y"),--($A$2:$F$2="y"))

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"Rick Rothstein (MVP - VB)" wrote in
message ...
Give this a try...

=SUMPRODUCT(--(A1:F1&A2:F2="yy"))

Rick


"twototango" wrote in message
...
I have several sheets setup from which I need to caluculate: how many of
the
cells in a range meet both criteria. It's setup something like the
below:

A B C D E F....
1 y y n n y y
2 n y n y y n

I need to determine how many "y" in range A1:F1 that are also "y" in
A2:F2
across multiple sheets.

Thanks!

calculate total number of items that meet 2 over multiple shee
 

How do I do a total across multiple sheets? I can't get this to work.
Thanks.

"Bob Phillips" wrote:

More than 10% quicker

=SUMPRODUCT(--($A$1:$F$1="y"),--($A$2:$F$2="y"))

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Rick Rothstein (MVP - VB)" wrote in
message ...
Give this a try...

=SUMPRODUCT(--(A1:F1&A2:F2="yy"))

Rick


"twototango" wrote in message
...
I have several sheets setup from which I need to caluculate: how many of
the
cells in a range meet both criteria. It's setup something like the
below:

A B C D E F....
1 y y n n y y
2 n y n y y n

I need to determine how many "y" in range A1:F1 that are also "y" in
A2:F2
across multiple sheets.

Thanks!

calculate total number of items that meet 2 over multiple sheets
 

I expected more I must admit, I thought that concatenating the whole range
would be a big overhead.

The difference between relative and absolute, aside from different results,
is a very small time difference. If anything, the absolute formulae were
slower in my tests, that I didn't expect.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Rick Rothstein (MVP - VB)" wrote in
message ...
Interesting... I would not have expected the time difference to be that
great. I guess it is the concatenation that slows it down. I would expect,
being a situational counting operation, that this SUMPRODUCT will probably
appear only one time (as opposed to being copied down), so my expectation
is that the time difference would not be significant. Out of curiosity,
does using the absolute references add anything to the time savings (that
is, is relative referencing slower than absolute referencing)?

Rick


"Bob Phillips" wrote in message
...
More than 10% quicker

=SUMPRODUCT(--($A$1:$F$1="y"),--($A$2:$F$2="y"))

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"Rick Rothstein (MVP - VB)" wrote
in message ...
Give this a try...

=SUMPRODUCT(--(A1:F1&A2:F2="yy"))

Rick


"twototango" wrote in message
...
I have several sheets setup from which I need to caluculate: how many of
the
cells in a range meet both criteria. It's setup something like the
below:

A B C D E F....
1 y y n n y y
2 n y n y y n

I need to determine how many "y" in range A1:F1 that are also "y" in
A2:F2
across multiple sheets.

Thanks!