Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMIF Criteria
I'm trying to use the SUMIF function with criteria that value must be 30 and
<=60. How would I code that with this function? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMIF Criteria
Use two sumif functions... one adds up everything over 30, the other
everything over 60. Subtract to get those in (30-60] =sumif(range,"30",sum_range) - sumif(range,"60",sum_range) "JC" wrote: I'm trying to use the SUMIF function with criteria that value must be 30 and <=60. How would I code that with this function? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMIF Criteria
For example, in A1 thru A5:
29 30 40 60 61 =SUMPRODUCT(A1:A5*(A1:A530)*(A1:A5<=60)) displays 100 -- Gary''s Student - gsnu200784 "JC" wrote: I'm trying to use the SUMIF function with criteria that value must be 30 and <=60. How would I code that with this function? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMIF Criteria
Try using sumproduct. suppose your data was in A1:A100 you would write the
formula =Sumproduct((A1:A10030)*(A1:A100<=60)*A1:A100) "JC" wrote: I'm trying to use the SUMIF function with criteria that value must be 30 and <=60. How would I code that with this function? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMIF Criteria
If your data is in Column A, would this formula work for you?
=IF(AND(SUM(A1330),SUM(A13<=60)),SUM(A13),0) Dan |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMIF Criteria
What value do the 3 uses of the SUM() function add to your formula, Dan?
What does that formula do that you wouldn't get from =IF(AND(A1330,A13<=60),A13,0) ? [You may perhaps like to look at Excel help for the SUM function, to remind yourself that SUM takes a list of arguments and SUMs them. If you give the SUM function only one argument, as in each of your 3 cases, the SUM is trivial.] Additionally, neither formula actually addresses the OP's requirement for a SUMIF function, as you haven't actually done a SUM, and you've addressed only one cell. Hence the SUMPRODUCT solution, or subtracting one SUMIF from another, would seem more appropriate. -- David Biddulph "dan dungan" wrote in message ... If your data is in Column A, would this formula work for you? =IF(AND(SUM(A1330),SUM(A13<=60)),SUM(A13),0) Dan |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sumif with criteria help | Excel Worksheet Functions | |||
Sumif with 2 criteria | Excel Discussion (Misc queries) | |||
nested sumif or sumif with two criteria | Excel Worksheet Functions | |||
SUMIF Criteria | Excel Discussion (Misc queries) | |||
sumif with 2 criteria | Excel Worksheet Functions |