Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT
The following formula works fine:
=SUMPRODUCT(--(INDIRECT($AW$3&"!$BL1:$BL$6000")={"A"}),--(INDIRECT($AW$3&"!$O$1:$O$6000")="XN01"),(INDIRECT ($AW$3&"!$CI$1:$CI$6000"))) However, I cant seem to get the following to work: =SUMPRODUCT(--(INDIRECT($AW$3&"!$BL1:$BL$6000")={"A","B"}),--(INDIRECT($AW$3&"!$O$1:$O$6000")="XN01","XR01"),(I NDIRECT($AW$3&"!$CI$1:$CI$6000"))) I need to be able to summarise (using a headcount indicator (1) in Column CI) groups of people together who are in certain groups e.g. staff can be in groups A,B or C in Column BL, and in groups XN01 or XR01 in column O. The indirect function is used as there are monthly named sheets with similar data in. Thanks |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT
Try it like this...
=SUMPRODUCT(--(ISNUMBER(MATCH(INDIRECT($AW$3&"!BL1:BL6000"),{"A" ,"B"},0))),--(ISNUMBER(MATCH(INDIRECT($AW$3&"!O1:O6000"),{"XN01 ","XR01"},0))),INDIRECT($AW$3&"!CI1:CI6000")) -- Biff Microsoft Excel MVP "JPDS" wrote in message ... The following formula works fine: =SUMPRODUCT(--(INDIRECT($AW$3&"!$BL1:$BL$6000")={"A"}),--(INDIRECT($AW$3&"!$O$1:$O$6000")="XN01"),(INDIRECT ($AW$3&"!$CI$1:$CI$6000"))) However, I cant seem to get the following to work: =SUMPRODUCT(--(INDIRECT($AW$3&"!$BL1:$BL$6000")={"A","B"}),--(INDIRECT($AW$3&"!$O$1:$O$6000")="XN01","XR01"),(I NDIRECT($AW$3&"!$CI$1:$CI$6000"))) I need to be able to summarise (using a headcount indicator (1) in Column CI) groups of people together who are in certain groups e.g. staff can be in groups A,B or C in Column BL, and in groups XN01 or XR01 in column O. The indirect function is used as there are monthly named sheets with similar data in. Thanks |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT
That works perfectly, now I have the arduous task of understanding why your
formula works and mine doesnt! What was wrong with my formula so I can understand it a bit more? Thanks again "T. Valko" wrote: Try it like this... =SUMPRODUCT(--(ISNUMBER(MATCH(INDIRECT($AW$3&"!BL1:BL6000"),{"A" ,"B"},0))),--(ISNUMBER(MATCH(INDIRECT($AW$3&"!O1:O6000"),{"XN01 ","XR01"},0))),INDIRECT($AW$3&"!CI1:CI6000")) -- Biff Microsoft Excel MVP "JPDS" wrote in message ... The following formula works fine: =SUMPRODUCT(--(INDIRECT($AW$3&"!$BL1:$BL$6000")={"A"}),--(INDIRECT($AW$3&"!$O$1:$O$6000")="XN01"),(INDIRECT ($AW$3&"!$CI$1:$CI$6000"))) However, I cant seem to get the following to work: =SUMPRODUCT(--(INDIRECT($AW$3&"!$BL1:$BL$6000")={"A","B"}),--(INDIRECT($AW$3&"!$O$1:$O$6000")="XN01","XR01"),(I NDIRECT($AW$3&"!$CI$1:$CI$6000"))) I need to be able to summarise (using a headcount indicator (1) in Column CI) groups of people together who are in certain groups e.g. staff can be in groups A,B or C in Column BL, and in groups XN01 or XR01 in column O. The indirect function is used as there are monthly named sheets with similar data in. Thanks . |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT
See if this helps:
http://xldynamic.com/source/xld.SUMPRODUCT.html If not, just let me know and I'll give you a "deluxe" explanation! -- Biff Microsoft Excel MVP "JPDS" wrote in message ... That works perfectly, now I have the arduous task of understanding why your formula works and mine doesnt! What was wrong with my formula so I can understand it a bit more? Thanks again "T. Valko" wrote: Try it like this... =SUMPRODUCT(--(ISNUMBER(MATCH(INDIRECT($AW$3&"!BL1:BL6000"),{"A" ,"B"},0))),--(ISNUMBER(MATCH(INDIRECT($AW$3&"!O1:O6000"),{"XN01 ","XR01"},0))),INDIRECT($AW$3&"!CI1:CI6000")) -- Biff Microsoft Excel MVP "JPDS" wrote in message ... The following formula works fine: =SUMPRODUCT(--(INDIRECT($AW$3&"!$BL1:$BL$6000")={"A"}),--(INDIRECT($AW$3&"!$O$1:$O$6000")="XN01"),(INDIRECT ($AW$3&"!$CI$1:$CI$6000"))) However, I cant seem to get the following to work: =SUMPRODUCT(--(INDIRECT($AW$3&"!$BL1:$BL$6000")={"A","B"}),--(INDIRECT($AW$3&"!$O$1:$O$6000")="XN01","XR01"),(I NDIRECT($AW$3&"!$CI$1:$CI$6000"))) I need to be able to summarise (using a headcount indicator (1) in Column CI) groups of people together who are in certain groups e.g. staff can be in groups A,B or C in Column BL, and in groups XN01 or XR01 in column O. The indirect function is used as there are monthly named sheets with similar data in. Thanks . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sumproduct with Condition OR Sumproduct with ADDRESS function - HE | Excel Discussion (Misc queries) | |||
Conditional SUMPRODUCT or SUMPRODUCT with Filters | Excel Worksheet Functions | |||
SUMPRODUCT Help | Excel Worksheet Functions | |||
sumproduct? sumif(sumproduct)? | Excel Worksheet Functions | |||
=SUMPRODUCT | Excel Worksheet Functions |