| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF with SUMIF ? Or ??
I have this formula that works for a partial need.
=SUMIF($F:$F,110,$G:$G) How can I modify the above formula to provide the above result only if the A column has #'s =100 and < 199 ? The A column has #'s from 0 thru <400. I need other ranges also, but if I get the above, I could figure out the others. Thanks much, Steve |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
IF with SUMIF ? Or ??
=SUMIF($F:$F,"=100",$G:$G)-SUMIF($F:$F,"=199",$G:$G)
-- --- HTH Bob (change the xxxx to gmail if mailing direct) "Steve" wrote in message ... I have this formula that works for a partial need. =SUMIF($F:$F,110,$G:$G) How can I modify the above formula to provide the above result only if the A column has #'s =100 and < 199 ? The A column has #'s from 0 thru <400. I need other ranges also, but if I get the above, I could figure out the others. Thanks much, Steve |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
IF with SUMIF ? Or ??
Try something like this:
=SUMIF($F:$F,"=100",$G:$G)-SUMIF($F:$F,"199",$G:$G) Does that help? *********** Regards, Ron XL2002, WinXP "Steve" wrote: I have this formula that works for a partial need. =SUMIF($F:$F,110,$G:$G) How can I modify the above formula to provide the above result only if the A column has #'s =100 and < 199 ? The A column has #'s from 0 thru <400. I need other ranges also, but if I get the above, I could figure out the others. Thanks much, Steve |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
IF with SUMIF ? Or ??
Whoops! I think Bob got it right.....mine incorrectly includes items =199
*********** Regards, Ron XL2002, WinXP "Ron Coderre" wrote: Try something like this: =SUMIF($F:$F,"=100",$G:$G)-SUMIF($F:$F,"199",$G:$G) Does that help? *********** Regards, Ron XL2002, WinXP "Steve" wrote: I have this formula that works for a partial need. =SUMIF($F:$F,110,$G:$G) How can I modify the above formula to provide the above result only if the A column has #'s =100 and < 199 ? The A column has #'s from 0 thru <400. I need other ranges also, but if I get the above, I could figure out the others. Thanks much, Steve |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
IF with SUMIF ? Or ??
I guess I didn't quite explain this completly. There's various #'s in the F
column, and I want the formula to work with any 110 in that column, which it currently does. That's where the:$F:$F,110 is from. In addition, I have various other #'s in the A column, and that where the 100 and <199 come from. Basically, if A is = 100 and <199 , and F has a 110, sum the corresponding #'s in the G colum that match the A & F criterea. Thanks, Steve "Ron Coderre" wrote: Try something like this: =SUMIF($F:$F,"=100",$G:$G)-SUMIF($F:$F,"199",$G:$G) Does that help? *********** Regards, Ron XL2002, WinXP "Steve" wrote: I have this formula that works for a partial need. =SUMIF($F:$F,110,$G:$G) How can I modify the above formula to provide the above result only if the A column has #'s =100 and < 199 ? The A column has #'s from 0 thru <400. I need other ranges also, but if I get the above, I could figure out the others. Thanks much, Steve |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
IF with SUMIF ? Or ??
Try this:
=SUMPRODUCT(--(A1:A10=100),--(A1:A10<199),--(F1:F10=110),G1:G10) Note: you can't use entire columns as range references - A:A Biff "Steve" wrote in message ... I guess I didn't quite explain this completly. There's various #'s in the F column, and I want the formula to work with any 110 in that column, which it currently does. That's where the:$F:$F,110 is from. In addition, I have various other #'s in the A column, and that where the 100 and <199 come from. Basically, if A is = 100 and <199 , and F has a 110, sum the corresponding #'s in the G colum that match the A & F criterea. Thanks, Steve "Ron Coderre" wrote: Try something like this: =SUMIF($F:$F,"=100",$G:$G)-SUMIF($F:$F,"199",$G:$G) Does that help? *********** Regards, Ron XL2002, WinXP "Steve" wrote: I have this formula that works for a partial need. =SUMIF($F:$F,110,$G:$G) How can I modify the above formula to provide the above result only if the A column has #'s =100 and < 199 ? The A column has #'s from 0 thru <400. I need other ranges also, but if I get the above, I could figure out the others. Thanks much, Steve |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
IF with SUMIF ? Or ??
Perfect !! Works like a charm. Thanks so much.
Steve "T. Valko" wrote: Try this: =SUMPRODUCT(--(A1:A10=100),--(A1:A10<199),--(F1:F10=110),G1:G10) Note: you can't use entire columns as range references - A:A Biff "Steve" wrote in message ... I guess I didn't quite explain this completly. There's various #'s in the F column, and I want the formula to work with any 110 in that column, which it currently does. That's where the:$F:$F,110 is from. In addition, I have various other #'s in the A column, and that where the 100 and <199 come from. Basically, if A is = 100 and <199 , and F has a 110, sum the corresponding #'s in the G colum that match the A & F criterea. Thanks, Steve "Ron Coderre" wrote: Try something like this: =SUMIF($F:$F,"=100",$G:$G)-SUMIF($F:$F,"199",$G:$G) Does that help? *********** Regards, Ron XL2002, WinXP "Steve" wrote: I have this formula that works for a partial need. =SUMIF($F:$F,110,$G:$G) How can I modify the above formula to provide the above result only if the A column has #'s =100 and < 199 ? The A column has #'s from 0 thru <400. I need other ranges also, but if I get the above, I could figure out the others. Thanks much, Steve |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
IF with SUMIF ? Or ??
You're welcome. Thanks for the feedback!
Biff "Steve" wrote in message ... Perfect !! Works like a charm. Thanks so much. Steve "T. Valko" wrote: Try this: =SUMPRODUCT(--(A1:A10=100),--(A1:A10<199),--(F1:F10=110),G1:G10) Note: you can't use entire columns as range references - A:A Biff "Steve" wrote in message ... I guess I didn't quite explain this completly. There's various #'s in the F column, and I want the formula to work with any 110 in that column, which it currently does. That's where the:$F:$F,110 is from. In addition, I have various other #'s in the A column, and that where the 100 and <199 come from. Basically, if A is = 100 and <199 , and F has a 110, sum the corresponding #'s in the G colum that match the A & F criterea. Thanks, Steve "Ron Coderre" wrote: Try something like this: =SUMIF($F:$F,"=100",$G:$G)-SUMIF($F:$F,"199",$G:$G) Does that help? *********** Regards, Ron XL2002, WinXP "Steve" wrote: I have this formula that works for a partial need. =SUMIF($F:$F,110,$G:$G) How can I modify the above formula to provide the above result only if the A column has #'s =100 and < 199 ? The A column has #'s from 0 thru <400. I need other ranges also, but if I get the above, I could figure out the others. Thanks much, Steve |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
IF with SUMIF ? Or ??
I used this formula after tweaking it in another application and it also
worked "perfectly" for me! Thank YOU! Could you explain the necessity for the use of the hyphens...I'm not sure why I use those to make this work? "T. Valko" wrote: Try this: =SUMPRODUCT(--(A1:A10=100),--(A1:A10<199),--(F1:F10=110),G1:G10) Note: you can't use entire columns as range references - A:A Biff "Steve" wrote in message ... I guess I didn't quite explain this completly. There's various #'s in the F column, and I want the formula to work with any 110 in that column, which it currently does. That's where the:$F:$F,110 is from. In addition, I have various other #'s in the A column, and that where the 100 and <199 come from. Basically, if A is = 100 and <199 , and F has a 110, sum the corresponding #'s in the G colum that match the A & F criterea. Thanks, Steve "Ron Coderre" wrote: Try something like this: =SUMIF($F:$F,"=100",$G:$G)-SUMIF($F:$F,"199",$G:$G) Does that help? *********** Regards, Ron XL2002, WinXP "Steve" wrote: I have this formula that works for a partial need. =SUMIF($F:$F,110,$G:$G) How can I modify the above formula to provide the above result only if the A column has #'s =100 and < 199 ? The A column has #'s from 0 thru <400. I need other ranges also, but if I get the above, I could figure out the others. Thanks much, Steve |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
IF with SUMIF ? Or ??
See http://xldynamic.com/source/xld.SUMPRODUCT.html
-- --- HTH Bob (change the xxxx to gmail if mailing direct) "Marge" wrote in message ... I used this formula after tweaking it in another application and it also worked "perfectly" for me! Thank YOU! Could you explain the necessity for the use of the hyphens...I'm not sure why I use those to make this work? "T. Valko" wrote: Try this: =SUMPRODUCT(--(A1:A10=100),--(A1:A10<199),--(F1:F10=110),G1:G10) Note: you can't use entire columns as range references - A:A Biff "Steve" wrote in message ... I guess I didn't quite explain this completly. There's various #'s in the F column, and I want the formula to work with any 110 in that column, which it currently does. That's where the:$F:$F,110 is from. In addition, I have various other #'s in the A column, and that where the 100 and <199 come from. Basically, if A is = 100 and <199 , and F has a 110, sum the corresponding #'s in the G colum that match the A & F criterea. Thanks, Steve "Ron Coderre" wrote: Try something like this: =SUMIF($F:$F,"=100",$G:$G)-SUMIF($F:$F,"199",$G:$G) Does that help? *********** Regards, Ron XL2002, WinXP "Steve" wrote: I have this formula that works for a partial need. =SUMIF($F:$F,110,$G:$G) How can I modify the above formula to provide the above result only if the A column has #'s =100 and < 199 ? The A column has #'s from 0 thru <400. I need other ranges also, but if I get the above, I could figure out the others. Thanks much, Steve |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
IF with SUMIF ? Or ??
Got it - thank you!
"Bob Phillips" wrote: See http://xldynamic.com/source/xld.SUMPRODUCT.html -- --- HTH Bob (change the xxxx to gmail if mailing direct) "Marge" wrote in message ... I used this formula after tweaking it in another application and it also worked "perfectly" for me! Thank YOU! Could you explain the necessity for the use of the hyphens...I'm not sure why I use those to make this work? "T. Valko" wrote: Try this: =SUMPRODUCT(--(A1:A10=100),--(A1:A10<199),--(F1:F10=110),G1:G10) Note: you can't use entire columns as range references - A:A Biff "Steve" wrote in message ... I guess I didn't quite explain this completly. There's various #'s in the F column, and I want the formula to work with any 110 in that column, which it currently does. That's where the:$F:$F,110 is from. In addition, I have various other #'s in the A column, and that where the 100 and <199 come from. Basically, if A is = 100 and <199 , and F has a 110, sum the corresponding #'s in the G colum that match the A & F criterea. Thanks, Steve "Ron Coderre" wrote: Try something like this: =SUMIF($F:$F,"=100",$G:$G)-SUMIF($F:$F,"199",$G:$G) Does that help? *********** Regards, Ron XL2002, WinXP "Steve" wrote: I have this formula that works for a partial need. =SUMIF($F:$F,110,$G:$G) How can I modify the above formula to provide the above result only if the A column has #'s =100 and < 199 ? The A column has #'s from 0 thru <400. I need other ranges also, but if I get the above, I could figure out the others. Thanks much, Steve |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How to use SUMIF to return sums between two values located in cells | Excel Worksheet Functions | |||
| How to use SUMIF function with non-adjacent cells | Excel Worksheet Functions | |||
| Sumif of Sumif perhaps? | Excel Discussion (Misc queries) | |||
| SUMIF | Excel Worksheet Functions | |||
| SUMIF - Range name to used for the "sum_range" portion of a SUMIF function | Excel Worksheet Functions | |||
Linear Mode
