| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multiple Conditions
I have a range in one sheet where
Sheet 1 (source data) row 1 (D1:BY1)=dept (say 1100 through 1800) Column B (B3:B277)=product code (d3:by277) contains values Sheet 2 (summary report) Row 2 (d2:ad2) = Department Column B (B4:B277) = Product Code I need to calculate the sum of the values by department and product code. I have tried = SUMPRODUCT('[Sheet1]Group Trial Balance - Current'!$D$1:$BY$1=E$2)*('[Sheet1]Group Trial Balance - Current'!$B$3:$B$277=$B144)*('[Sheet1]Group Trial Balance - Current'!$D$3:$BY$277) but I am not getting the correct value Please and Thanks |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Multiple Conditions
try this
=SUMPRODUCT((sheet1!$B$3:$B$277=Sheet2!$B4)*(sheet 1!$D$1:$BY$1=Sheet2!D $2)*(sheet1!$D$3:$BY$277)) On Oct 22, 12:45*am, Curtis wrote: I have a range in one sheet where Sheet 1 (source data) row 1 (D1:BY1)=dept (say 1100 through 1800) Column B (B3:B277)=product code (d3:by277) contains values Sheet 2 (summary report) Row 2 (d2:ad2) = Department Column B (B4:B277) = Product Code I need to calculate the sum of the values by department and product code. I have tried = SUMPRODUCT('[Sheet1]Group Trial Balance - Current'!$D$1:$BY$1=E$2)*('[Sheet1]Group Trial Balance - Current'!$B$3:$B$277=$B144)*('[Sheet1]Group Trial Balance - Current'!$D$3:$BY$277) but I am not getting the correct value Please and Thanks |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Multiple Conditions
Disregard... Figured it out
Thanks "Curtis" wrote: I have a range in one sheet where Sheet 1 (source data) row 1 (D1:BY1)=dept (say 1100 through 1800) Column B (B3:B277)=product code (d3:by277) contains values Sheet 2 (summary report) Row 2 (d2:ad2) = Department Column B (B4:B277) = Product Code I need to calculate the sum of the values by department and product code. I have tried = SUMPRODUCT('[Sheet1]Group Trial Balance - Current'!$D$1:$BY$1=E$2)*('[Sheet1]Group Trial Balance - Current'!$B$3:$B$277=$B144)*('[Sheet1]Group Trial Balance - Current'!$D$3:$BY$277) but I am not getting the correct value Please and Thanks |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Setting multiple conditions to return a figure from multiple cells | Excel Discussion (Misc queries) | |||
| SUMPRODUCT or INDEX/MATCH for multiple conditions and multiple rec | Excel Discussion (Misc queries) | |||
| How do I add multiple values that match multiple conditions? | Excel Discussion (Misc queries) | |||
| Combining Text from multiple cells under multiple conditions | Excel Worksheet Functions | |||
| How to multiple conditions to validate more than 2 conditions to . | Excel Worksheet Functions | |||
Linear Mode
