| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
cell reference in sumifs
Hi,
I want to use =SUM(SUMIFS(W:W,L:L,{"DR","SS"},S:S,B5)) which works but I want the {"DR","SS"} arguement to come from a cell reference. i.e =SUM(SUMIFS(W:W,L:L,C5,S:S,B5)) where C5 is {"DR","SS"}. Unfortunately this isn't working for me. Any suggestions? Thanks. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
cell reference in sumifs
C5: holds DR
D5: holds SS =SUMPRODUCT(--(ISNUMBER(MATCH(L:L,C5:D5,0))),--(S:S=B5),W:W) "Jai" wrote: Hi, I want to use =SUM(SUMIFS(W:W,L:L,{"DR","SS"},S:S,B5)) which works but I want the {"DR","SS"} arguement to come from a cell reference. i.e =SUM(SUMIFS(W:W,L:L,C5,S:S,B5)) where C5 is {"DR","SS"}. Unfortunately this isn't working for me. Any suggestions? Thanks. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
cell reference in sumifs
Thanks, but the problem is I need to use only one cell as the reference.
"Teethless mama" wrote: C5: holds DR D5: holds SS =SUMPRODUCT(--(ISNUMBER(MATCH(L:L,C5:D5,0))),--(S:S=B5),W:W) "Jai" wrote: Hi, I want to use =SUM(SUMIFS(W:W,L:L,{"DR","SS"},S:S,B5)) which works but I want the {"DR","SS"} arguement to come from a cell reference. i.e =SUM(SUMIFS(W:W,L:L,C5,S:S,B5)) where C5 is {"DR","SS"}. Unfortunately this isn't working for me. Any suggestions? Thanks. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
cell reference in sumifs
C5: holds DR,SS
=SUMPRODUCT((ISNUMBER(SEARCH(L1:L100,C5)))*(S1:S10 0=B5),W1:W100) Adjust range to suit "Jai" wrote: Thanks, but the problem is I need to use only one cell as the reference. "Teethless mama" wrote: C5: holds DR D5: holds SS =SUMPRODUCT(--(ISNUMBER(MATCH(L:L,C5:D5,0))),--(S:S=B5),W:W) "Jai" wrote: Hi, I want to use =SUM(SUMIFS(W:W,L:L,{"DR","SS"},S:S,B5)) which works but I want the {"DR","SS"} arguement to come from a cell reference. i.e =SUM(SUMIFS(W:W,L:L,C5,S:S,B5)) where C5 is {"DR","SS"}. Unfortunately this isn't working for me. Any suggestions? Thanks. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
cell reference in sumifs
C5: holds DR,SS
That'll be treated as a single literal string. Based on the formula the OP posted and their follow-up description, it sounds like they want to use a single cell to hold 2 separate criteria. While it could be done I would recommend they not do it that way. -- Biff Microsoft Excel MVP "Teethless mama" wrote in message ... C5: holds DR,SS =SUMPRODUCT((ISNUMBER(SEARCH(L1:L100,C5)))*(S1:S10 0=B5),W1:W100) Adjust range to suit "Jai" wrote: Thanks, but the problem is I need to use only one cell as the reference. "Teethless mama" wrote: C5: holds DR D5: holds SS =SUMPRODUCT(--(ISNUMBER(MATCH(L:L,C5:D5,0))),--(S:S=B5),W:W) "Jai" wrote: Hi, I want to use =SUM(SUMIFS(W:W,L:L,{"DR","SS"},S:S,B5)) which works but I want the {"DR","SS"} arguement to come from a cell reference. i.e =SUM(SUMIFS(W:W,L:L,C5,S:S,B5)) where C5 is {"DR","SS"}. Unfortunately this isn't working for me. Any suggestions? Thanks. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| sumifs will not work with a cell reference as criteria | Excel Discussion (Misc queries) | |||
| Sumifs - greater than criteria with a Cell Reference | Excel Worksheet Functions | |||
| absolute cell reference A spreadsheet cell reference that does no | Excel Discussion (Misc queries) | |||
| problem with cell reference in =sum(offset(cell reference,x,y,z,a)). Want cell ref to be variable. | Excel Worksheet Functions | |||
| Problem with =sum(offset(cell reference,w,x,y,z). I want cell reference to be variable | Excel Worksheet Functions | |||
Linear Mode
