sumif statements

V

vivi

Hi there

I am trying to create a report in order to calculate spends per customer
which consists of two work sheets –

The first worksheet has all the sales data as well as the sites and customer
mixed in the same worksheet

The second sheet need to summarize the data and need to lookup the
merchandised category in column A, customer type in column B and the sites in
column C all from sheet 1and enter the total value in column D in sheet 2. I
know I need to use a conditional sum statement for this but not so sure how
to do it, can any body help me?
 
B

Bob Phillips

=SUMPRODUCT(--(Sheet1!A1:A100=A1),--(Sheet1!B1:B100=B1),--(Sheet1!C:C00=C1),
Sheet1!D1:D100)

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)
 
V

vivi

thanks for your help...i've tested he formula out but it has #NUM! instead
the sumup value which should be 96?.....any ideas??thanks
 
B

Bob Phillips

Sorry, my mistake, try this

=SUMPRODUCT(--(Sheet1!A1:A100=A1),--(Sheet1!B1:B100=B1),
--(Sheet1!C1:C100=C1),Sheet1!D1:D100)

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)
 
V

vivi

Hi Bob..I've tried that too....still not working...isn't it sum product is
used for mutliplication? my column A B and C are all text...will that change
anything?

Sorry about this prolonged issue..and thanks for your help and patients...

Vivi
 
A

Aladin Akyurek

In E2 on the first worksheet (say, Sheet1) enter & copy down:

=A2&"#"&B2&"#"&C2

If you are on Excel 2003, convert the data area in A:E into a list by
means of Data|List|Create List.

On the second sheet (say, Sheet2), you can invoke an ordinary SumIf formula:

=SUMIF(Sheet1!$E$1000,A2&"#"&B2&"#"&C2,Sheet1!$D$2:$D$1000)

where A2 on Sheet2 houses a merchandized category of interest, B2 a
customer type, and C2 a site
 
B

Bob Phillips

Did you adjust the formula to your data?

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)
 
Top