Hello - I am trying to generate a report based on some usage reports. I
have three columns, one with user names, one with usage description and
the final with corresponding usage quantity. Some of the usage
descriptions are synonymous so the usage quantity will be a sum of
synonymous categories. For example:

A B C
Green X 4
Green Y 10
Green Z 5
Smith X 3
Smith Y 5
Smith Z 2

In this example, column B has the usage description and for our report
we see X & Y as synonymous -- we would like to see usage results that
are the sum of these. E.g.

Green X/Y 14
Green Z 10
Smith X/Y 8
Smith Z 2
Etc.

This seems like a pretty straightforward =SUMIF problem
[=SUM(IF(A1:A$="Smith",IF(B1:B$="X,Y",C1:C$,0),0))] BUT there is of
course a caveat... The list of names is over 100 and I'd prefer to NOT
declare each one. Is there a way that I can use this formula where I
look for unique values in column A to groupby and then sum accordingly?

You could use SUMPRODUCT
See explanations at
http://www.xldynamic.com/source/xld.SUMPRODUCT.html
http://mcgimpsey.com/excel/formulae/doubleneg.html

I used 6 rows to check my formulas, just change row numbers as needed.
You CANNOT use entire row (A:A) with SUMPRODUCT

Without the synonym problem to start with,
use =SUMPRODUCT(--(A1:A6="Green"),--(B1:B6="X"),C1:C6)
When X and Y are to be aggregated
=SUMPRODUCT(--(A1:A6="Green"),((B1:B6="X")+(B1:B6="Y")),C1:C6)
or
=SUMPRODUCT(--(A1:A6="Green"),--(B1:B6="X"),C1:C6)+SUMPRODUCT(--(A1:A6="Green"),--(B1:B6="Y"),C1:C6)

But you do not want to enter names. Use a Pivot Table.
To overcome X=Y
(a) Use a helper column with =IF(OR(A1="X",A1="Y"), "X", A1)
or (b) extract data from pivot table, adding the X and Y columns


Not really a "macro" so why asked in this newsgroup rather than
Worksheetfunctions ?
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

wrote in message
oups.com...
Hello - I am trying to generate a report based on some usage reports. I
have three columns, one with user names, one with usage description and
the final with corresponding usage quantity. Some of the usage
descriptions are synonymous so the usage quantity will be a sum of
synonymous categories. For example:

A B C
Green X 4
Green Y 10
Green Z 5
Smith X 3
Smith Y 5
Smith Z 2

In this example, column B has the usage description and for our report
we see X & Y as synonymous -- we would like to see usage results that
are the sum of these. E.g.

Green X/Y 14
Green Z 10
Smith X/Y 8
Smith Z 2
Etc.

This seems like a pretty straightforward =SUMIF problem
[=SUM(IF(A1:A$="Smith",IF(B1:B$="X,Y",C1:C$,0),0))] BUT there is of
course a caveat... The list of names is over 100 and I'd prefer to NOT
declare each one. Is there a way that I can use this formula where I
look for unique values in column A to groupby and then sum accordingly?

I would suggest using a pivot table. (this would produce all the
combinations when used with the next suggestion)

In the base data, I would create a dummy column that combines categories
using a formula/lookup table.

--
Regards,
Tom Ogilvy



" wrote:

Hello - I am trying to generate a report based on some usage reports. I
have three columns, one with user names, one with usage description and
the final with corresponding usage quantity. Some of the usage
descriptions are synonymous so the usage quantity will be a sum of
synonymous categories. For example:

A B C
Green X 4
Green Y 10
Green Z 5
Smith X 3
Smith Y 5
Smith Z 2

In this example, column B has the usage description and for our report
we see X & Y as synonymous -- we would like to see usage results that
are the sum of these. E.g.

Green X/Y 14
Green Z 10
Smith X/Y 8
Smith Z 2
Etc.

This seems like a pretty straightforward =SUMIF problem
[=SUM(IF(A1:A$="Smith",IF(B1:B$="X,Y",C1:C$,0),0))] BUT there is of
course a caveat... The list of names is over 100 and I'd prefer to NOT
declare each one. Is there a way that I can use this formula where I
look for unique values in column A to groupby and then sum accordingly?

I just created a pivot table that does what you want. Insert a "calculated
formula" equal to X+Y and hide the X and Y fields and it should work for
you.

hth,

Doug


wrote in message
oups.com...
Hello - I am trying to generate a report based on some usage reports. I
have three columns, one with user names, one with usage description and
the final with corresponding usage quantity. Some of the usage
descriptions are synonymous so the usage quantity will be a sum of
synonymous categories. For example:

A B C
Green X 4
Green Y 10
Green Z 5
Smith X 3
Smith Y 5
Smith Z 2

In this example, column B has the usage description and for our report
we see X & Y as synonymous -- we would like to see usage results that
are the sum of these. E.g.

Green X/Y 14
Green Z 10
Smith X/Y 8
Smith Z 2
Etc.

This seems like a pretty straightforward =SUMIF problem
[=SUM(IF(A1:A$="Smith",IF(B1:B$="X,Y",C1:C$,0),0))] BUT there is of
course a caveat... The list of names is over 100 and I'd prefer to NOT
declare each one. Is there a way that I can use this formula where I
look for unique values in column A to groupby and then sum accordingly?

thanks for everyones help. The pivot table works great for this data.
The only caveat now is this:

my user data is supplied on a monthly basis - so I've created monthly
pivot table / reports. I've been looking around this board to figure
out how to combine these months to generate quarterly, semi anual, etc.
reports. In particular, it would be nice to see what individual users
are doing over a series of months. It looks like I can COMBINE these
reports as long as the fields are the same, BUT I want to keep the data
associated with the worksheet which in this case is associated with the
month.

When I try to setup a new pivot table / report from "multiple
consolidation ranges" I browse for the appropriate work sheets and then
get an error... Maybe I'm doing this wrong. Perhaps the data should
not be structured? Any advice / help is greatly appreciated.

Thanks!

Hi,

Take a look at Debra's brilliant site
http://www.contextures.com/xlPivot08.html

HTH
Cheers
Carim