|
conditional counting
Here is my data (in actuality, it's about 35,000 rows long and will be
growing at the rate of 1,000 rows per month) and it's in a sheet I've labeled as "Format 1". TYPE (column A) PERIOD (column B) VERSION (column C) DS 02/2005 1 BS 02/2005 2 WS 03/2005 2 DS 02/2005 1 I want to be able to count all of the different combinations that occur (i.e., count all of the "DS and 02/2005 and 1") and return them in a sheet I've labeled "Results" There is too much data for a pivot table and I know there's a solution using sumproduct (or something like that), but I haven't been able to figure it out. Any ideas? Thanks, Jim |
Put DS in M1, the date in M2, version in M3 and use
=SUMPRODUCT(--(A2:A12000=M1),--(B2:B12000=M2),--(C2:C12000=M3)) -- HTH Bob Phillips "jim314" wrote in message ... Here is my data (in actuality, it's about 35,000 rows long and will be growing at the rate of 1,000 rows per month) and it's in a sheet I've labeled as "Format 1". TYPE (column A) PERIOD (column B) VERSION (column C) DS 02/2005 1 BS 02/2005 2 WS 03/2005 2 DS 02/2005 1 I want to be able to count all of the different combinations that occur (i.e., count all of the "DS and 02/2005 and 1") and return them in a sheet I've labeled "Results" There is too much data for a pivot table and I know there's a solution using sumproduct (or something like that), but I haven't been able to figure it out. Any ideas? Thanks, Jim |
Thanks Bob, that worked like a charm.
Now, let's say I have data in column D that I want to sum if the below conditions are met. How would I write a formula to do that? What I'm headed for here is an 'averageif' (which I have read is a sumif/countif). Thanks, Jim "Bob Phillips" wrote: Put DS in M1, the date in M2, version in M3 and use =SUMPRODUCT(--(A2:A12000=M1),--(B2:B12000=M2),--(C2:C12000=M3)) -- HTH Bob Phillips "jim314" wrote in message ... Here is my data (in actuality, it's about 35,000 rows long and will be growing at the rate of 1,000 rows per month) and it's in a sheet I've labeled as "Format 1". TYPE (column A) PERIOD (column B) VERSION (column C) DS 02/2005 1 BS 02/2005 2 WS 03/2005 2 DS 02/2005 1 I want to be able to count all of the different combinations that occur (i.e., count all of the "DS and 02/2005 and 1") and return them in a sheet I've labeled "Results" There is too much data for a pivot table and I know there's a solution using sumproduct (or something like that), but I haven't been able to figure it out. Any ideas? Thanks, Jim |
Just use Average itself
=AVERAGE(IF(A2:A12000&""=M1)*(B2:B12000=M2)*(C2:C1 2000=M3),D2:D12000) which is an array formula, so commit with Ctrl-Shift-Enter -- HTH Bob Phillips "jim314" wrote in message ... Thanks Bob, that worked like a charm. Now, let's say I have data in column D that I want to sum if the below conditions are met. How would I write a formula to do that? What I'm headed for here is an 'averageif' (which I have read is a sumif/countif). Thanks, Jim "Bob Phillips" wrote: Put DS in M1, the date in M2, version in M3 and use =SUMPRODUCT(--(A2:A12000=M1),--(B2:B12000=M2),--(C2:C12000=M3)) -- HTH Bob Phillips "jim314" wrote in message ... Here is my data (in actuality, it's about 35,000 rows long and will be growing at the rate of 1,000 rows per month) and it's in a sheet I've labeled as "Format 1". TYPE (column A) PERIOD (column B) VERSION (column C) DS 02/2005 1 BS 02/2005 2 WS 03/2005 2 DS 02/2005 1 I want to be able to count all of the different combinations that occur (i.e., count all of the "DS and 02/2005 and 1") and return them in a sheet I've labeled "Results" There is too much data for a pivot table and I know there's a solution using sumproduct (or something like that), but I haven't been able to figure it out. Any ideas? Thanks, Jim |
Can you double-check that formula? I'm getting an error at the M1 part.
Thanks "Bob Phillips" wrote: Just use Average itself =AVERAGE(IF(A2:A12000&""=M1)*(B2:B12000=M2)*(C2:C1 2000=M3),D2:D12000) which is an array formula, so commit with Ctrl-Shift-Enter -- HTH Bob Phillips "jim314" wrote in message ... Thanks Bob, that worked like a charm. Now, let's say I have data in column D that I want to sum if the below conditions are met. How would I write a formula to do that? What I'm headed for here is an 'averageif' (which I have read is a sumif/countif). Thanks, Jim "Bob Phillips" wrote: Put DS in M1, the date in M2, version in M3 and use =SUMPRODUCT(--(A2:A12000=M1),--(B2:B12000=M2),--(C2:C12000=M3)) -- HTH Bob Phillips "jim314" wrote in message ... Here is my data (in actuality, it's about 35,000 rows long and will be growing at the rate of 1,000 rows per month) and it's in a sheet I've labeled as "Format 1". TYPE (column A) PERIOD (column B) VERSION (column C) DS 02/2005 1 BS 02/2005 2 WS 03/2005 2 DS 02/2005 1 I want to be able to count all of the different combinations that occur (i.e., count all of the "DS and 02/2005 and 1") and return them in a sheet I've labeled "Results" There is too much data for a pivot table and I know there's a solution using sumproduct (or something like that), but I haven't been able to figure it out. Any ideas? Thanks, Jim |
Sorry, don't know what happened there
=AVERAGE(IF((A2:A12000&""=M1)*(B2:B12000=M2)*(C2:C 12000=M3),D2:D12000)) still array entered. -- HTH Bob Phillips "jim314" wrote in message ... Can you double-check that formula? I'm getting an error at the M1 part. Thanks "Bob Phillips" wrote: Just use Average itself =AVERAGE(IF(A2:A12000&""=M1)*(B2:B12000=M2)*(C2:C1 2000=M3),D2:D12000) which is an array formula, so commit with Ctrl-Shift-Enter -- HTH Bob Phillips "jim314" wrote in message ... Thanks Bob, that worked like a charm. Now, let's say I have data in column D that I want to sum if the below conditions are met. How would I write a formula to do that? What I'm headed for here is an 'averageif' (which I have read is a sumif/countif). Thanks, Jim "Bob Phillips" wrote: Put DS in M1, the date in M2, version in M3 and use =SUMPRODUCT(--(A2:A12000=M1),--(B2:B12000=M2),--(C2:C12000=M3)) -- HTH Bob Phillips "jim314" wrote in message ... Here is my data (in actuality, it's about 35,000 rows long and will be growing at the rate of 1,000 rows per month) and it's in a sheet I've labeled as "Format 1". TYPE (column A) PERIOD (column B) VERSION (column C) DS 02/2005 1 BS 02/2005 2 WS 03/2005 2 DS 02/2005 1 I want to be able to count all of the different combinations that occur (i.e., count all of the "DS and 02/2005 and 1") and return them in a sheet I've labeled "Results" There is too much data for a pivot table and I know there's a solution using sumproduct (or something like that), but I haven't been able to figure it out. Any ideas? Thanks, Jim |
| All times are GMT +1. The time now is 12:42 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com