Excel 2003 - SUMIF or other Solution

[Craig Brandt]

Hi Guys:
Here's my dilemma:

I have a flat file (example follows):



Orders

A B C

1 Client Product Quantity

2 Jim Green 23

3 Sam Blue 76

4 Jim Blue 13

5 Jim Green 54

..

..

..





I would like to create a matrix that looks like this:



Totals

A B C D

1 Green Blue Red

2 Jim 77 13

3 Sam 76

4 Jane





Is there a single formula that I can place is "Totals B2" that can give me
the desired results? The first thing that came to mind is SUMIF, but I haven
't figured out how to make it work.



Any assistance would be greatly appreciated,

Craig

 

[Nick Hodge]

Craig

Go to Data>Pivot Table... and make one of those. Add the Client Column as a
row field, the product as a column field and the Quantity as the 'Value'.

I think that'll give you what you want, without formula

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)
web: www.excelusergroup.org
web: www.nickhodge.co.uk

 

[Roger Govier]

Hi Craig

As Nick has said, Pivot Table is the easiest and quickest way to go.
If you did want a formula solution, then assuming your output matrix was in
the range G1:J4 it would be
=SUMPRODUCT(($A$2:$A$5=$G2)*($B$2:$B$5=H$1)*$C$2:$C$5)
placed in cell G2 and copied across and down as required

 

[Craig Brandt]

Roger & Nick:

I had considered Pivot Tables but am not prepared to create them under VBA
control. My comfort level with pivot tables is not quite there.
I had to chew on the SUMPRODUCT solution for awhile, but now understand why
it works and will incorporate this solution into the worksheet.

Thank you both for your suggestions,
Craig