Sumproduct

[Curtis]

SHeet 2

Column a (rows 13 thru 49) contains products
COlumn c (rows 13 thru 49) contains vendor
Column Q (rows 13 thru 49) contains spend

Sheet 1

Column a contains products
column b contains vendor
column c contains spend

I need a formula that will calculate the toatal spend by product by vendor

thanks

 

[Per Jessen]

Hi

I assume you want to calculate total based on values in Sheet2

In Sheet1 use this formula (headings in row 1):

=Sumproduct(--(Sheet2!A13:A49=A2), --(Sheet2!B13:B49=B2),Sheet2!
C13;C49)

Hopes this helps.
....
Per

 

[Curtis]

Yes to your first question.... However the formula below give me a value of 0.

The formual I used is
=SUMPRODUCT(--('Aug''09'!$A$13:$A$49=$A8),--('Aug''09'!$A$13:$A$49="ABC"),'Aug''09'!$Q$13:$Q$49)

In sheet I want to know how much money I spent on wigit 1 2 3 by vendor ABC

Sheet 2 have the widgit type in column 1, the vendor ABC in column b and the
spend in Q... Is it a formatting thing???

Thanks

 

[Curtis]

Sorry did noit work as it produces a value of zero. I used the fortmaula
below as you recommended with the mionr change of the second condition must
equal a certain vendor name

=SUMPRODUCT(--('Aug''09'!$A$13:$A$49=$A8),--('Aug''09'!$A$13:$A$49="TVC"),'Aug''09'!$Q$13:$Q$49)

Sheet 2 (data Sheet)

Column a = part number
Column B = vendor name
Column q = Spend $$

Sheet 1 is summary sheet

column a = all part numbers
column b = vendors
column c = what the spend is

Maybe I have a formating issue?

Thanks

 

[Curtis]

It was a fromatting issue

Thanks

Sorry did noit work as it produces a value of zero. I used the fortmaula
below as you recommended with the mionr change of the second condition must
equal a certain vendor name

=SUMPRODUCT(--('Aug''09'!$A$13:$A$49=$A8),--('Aug''09'!$A$13:$A$49="TVC"),'Aug''09'!$Q$13:$Q$49)

Sheet 2 (data Sheet)

Column a = part number
Column B = vendor name
Column q = Spend $$

Sheet 1 is summary sheet

column a = all part numbers
column b = vendors
column c = what the spend is

Maybe I have a formating issue?

Thanks

 

[David Biddulph]

If the vendor colunmn is column B, then your ABC test needs to refer to
$B$13:$B$49, not $A$13:$A$49
And if the sheet name is Aug 09, the formula should include 'Aug 09', not
'Aug''09'.