Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional SUMPRODUCT or SUMPRODUCT with Filters | Excel Worksheet Functions | |||
Sumproduct | Excel Discussion (Misc queries) | |||
sumproduct? sumif(sumproduct)? | Excel Worksheet Functions | |||
sumproduct | Excel Discussion (Misc queries) | |||
SUMPRODUCT | Excel Discussion (Misc queries) |