Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using SUMIF Function with a named cell reference as value in CRITE
I am trying to use a literal cell reference as a part of the criteria in the
SUMIF function, and i can't get it to sum properly. It only seems to like an actual value, not a cell reference, to work properly. Does anyone know a roundabout for this? Sample table: A B C 1 2 2 100 5 3 15 5 4 10 4 5 5 4 6 15 3 7 10 3 8 5 2 9 25 2 10 10 1 11 3 0 SUMIF(B2:B11,"=2",A2:A11) works fine SUMIF(B2:B11,"=$C$1",A2:All) doesn't work, returns a value of 0 in another spreadsheet (not the same cell values as the example, but the same structure). I want to use C1 so i can change the criteria in the SUMIF without having to change each and every reiteration of the SUMIF i have in different subsections of the worksheet. C1 can equal 0, 1, 2, 3, 4, 5. any recommendations? have you run into this issue before? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using SUMIF Function with a named cell reference as value in CRITE
Try =SUMIF(B2:B11,"="&$C$1,A2:A11)
With the $C$1 in quotes, it's treated as a literal, but you need to have the cell reference evaluated. "smilingindigo" wrote: I am trying to use a literal cell reference as a part of the criteria in the SUMIF function, and i can't get it to sum properly. It only seems to like an actual value, not a cell reference, to work properly. Does anyone know a roundabout for this? Sample table: A B C 1 2 2 100 5 3 15 5 4 10 4 5 5 4 6 15 3 7 10 3 8 5 2 9 25 2 10 10 1 11 3 0 SUMIF(B2:B11,"=2",A2:A11) works fine SUMIF(B2:B11,"=$C$1",A2:All) doesn't work, returns a value of 0 in another spreadsheet (not the same cell values as the example, but the same structure). I want to use C1 so i can change the criteria in the SUMIF without having to change each and every reiteration of the SUMIF i have in different subsections of the worksheet. C1 can equal 0, 1, 2, 3, 4, 5. any recommendations? have you run into this issue before? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using SUMIF Function with a named cell reference as value in CRITE
Hi!
Try it this way: =SUMIF(B2:B11,"="&$C$1,A2:A11) Biff "smilingindigo" wrote in message ... I am trying to use a literal cell reference as a part of the criteria in the SUMIF function, and i can't get it to sum properly. It only seems to like an actual value, not a cell reference, to work properly. Does anyone know a roundabout for this? Sample table: A B C 1 2 2 100 5 3 15 5 4 10 4 5 5 4 6 15 3 7 10 3 8 5 2 9 25 2 10 10 1 11 3 0 SUMIF(B2:B11,"=2",A2:A11) works fine SUMIF(B2:B11,"=$C$1",A2:All) doesn't work, returns a value of 0 in another spreadsheet (not the same cell values as the example, but the same structure). I want to use C1 so i can change the criteria in the SUMIF without having to change each and every reiteration of the SUMIF i have in different subsections of the worksheet. C1 can equal 0, 1, 2, 3, 4, 5. any recommendations? have you run into this issue before? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using SUMIF Function with a named cell reference as value in CRITE
Try it as: =SUMIF(B2:B11,"="&$C$1,A2:A11)
-- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "smilingindigo" wrote: I am trying to use a literal cell reference as a part of the criteria in the SUMIF function, and i can't get it to sum properly. It only seems to like an actual value, not a cell reference, to work properly. Does anyone know a roundabout for this? Sample table: A B C 1 2 2 100 5 3 15 5 4 10 4 5 5 4 6 15 3 7 10 3 8 5 2 9 25 2 10 10 1 11 3 0 SUMIF(B2:B11,"=2",A2:A11) works fine SUMIF(B2:B11,"=$C$1",A2:All) doesn't work, returns a value of 0 in another spreadsheet (not the same cell values as the example, but the same structure). I want to use C1 so i can change the criteria in the SUMIF without having to change each and every reiteration of the SUMIF i have in different subsections of the worksheet. C1 can equal 0, 1, 2, 3, 4, 5. any recommendations? have you run into this issue before? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Multiple cell reference IF function | Excel Worksheet Functions | |||
Custom functions calculating time arguments Help Desperate | Excel Worksheet Functions | |||
Cell Reference in Function | Excel Worksheet Functions | |||
Offset Function works in cell, not in named range | Excel Worksheet Functions | |||
Possible Lookup Table | Excel Worksheet Functions |