Sumproduct
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 |
Sumproduct
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 On 16 Jul., 01:11, Curtis wrote: 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 |
Sumproduct
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$4 9) 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 "Per Jessen" wrote: 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 On 16 Jul., 01:11, Curtis wrote: 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 |
Sumproduct
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$4 9) 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 "Per Jessen" wrote: 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 On 16 Jul., 01:11, Curtis wrote: 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 |
Sumproduct
It was a fromatting issue
Thanks "Curtis" wrote: 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$4 9) 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 "Per Jessen" wrote: 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 On 16 Jul., 01:11, Curtis wrote: 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 |
Sumproduct
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'. -- David Biddulph Curtis wrote: 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$4 9) 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 "Per Jessen" wrote: 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 On 16 Jul., 01:11, Curtis wrote: 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 |
All times are GMT +1. The time now is 08:16 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com