| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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! |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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! |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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! |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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! |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Summary Sheet - Total Multiple Sheets | Excel Discussion (Misc queries) | |||
| How do I get the total number of items that meet 2 criteria in Exc | Excel Worksheet Functions | |||
| HOW TO CALCULATE 2/10 OF 1 PERCENT OF A TOTAL NUMBER? | Excel Worksheet Functions | |||
| running total from the same field on multiple sheets as i add she | Excel Worksheet Functions | |||
| Rounding a number to a multiple quantity that adds to a fixed total number | Excel Worksheet Functions | |||
Linear Mode
