calculate total number of items that meet 2 over multiple sheets

[twototango]

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!

 

[Rick Rothstein \(MVP - VB\)]

Give this a try...

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

Rick

 

[Bob Phillips]

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\)]

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

 

[twototango]

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

 

[Bob Phillips]

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)