| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with SUMIF function
Hi,
I posted a problem concerning the SUMIF formula yesterday. The problem has slighly changed. I want the SUMIF formula to add the values in KolA only if the sum of KolA-(KolB + KolC) is greater then 0. The comparison is to be down for each row. If I, for example, have the following numbers: Kol A Kol B KolC 100 25 30 60 40 30 25 20 15 In the above example the SUMIF formula should return 85. I need to use the SUMIF formula since the result should only be calculated for rows not hidden by the autofilter function. Regards PO |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Help with SUMIF function
Try:
=SUM(IF(A2-(B2+C2)0,A2)) entered as an array formula, using Ctrl+Shift+Enter which will place curly brackets {} rounf the formula. And shouldn't the answer be 100? "PO" wrote: Hi, I posted a problem concerning the SUMIF formula yesterday. The problem has slighly changed. I want the SUMIF formula to add the values in KolA only if the sum of KolA-(KolB + KolC) is greater then 0. The comparison is to be down for each row. If I, for example, have the following numbers: Kol A Kol B KolC 100 25 30 60 40 30 25 20 15 In the above example the SUMIF formula should return 85. I need to use the SUMIF formula since the result should only be calculated for rows not hidden by the autofilter function. Regards PO |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Help with SUMIF function
sorry ... try:
=SUM(IF(A2:A4-(B2:B4+C2:C4)0,A2:A4)) entered as an array formula, using Ctrl+Shift+Enter which will place curly brackets {} rounf the formula. And shouldn't the answer be 100? "Toppers" wrote: Try: =SUM(IF(A2-(B2+C2)0,A2)) entered as an array formula, using Ctrl+Shift+Enter which will place curly brackets {} rounf the formula. And shouldn't the answer be 100? "PO" wrote: Hi, I posted a problem concerning the SUMIF formula yesterday. The problem has slighly changed. I want the SUMIF formula to add the values in KolA only if the sum of KolA-(KolB + KolC) is greater then 0. The comparison is to be down for each row. If I, for example, have the following numbers: Kol A Kol B KolC 100 25 30 60 40 30 25 20 15 In the above example the SUMIF formula should return 85. I need to use the SUMIF formula since the result should only be calculated for rows not hidden by the autofilter function. Regards PO |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Help with SUMIF function
Hi,
Thanks for your answer. You're right the result should be 100. The problem with your formula is that it doesn't work together with autofilter. I have to use SUMIF because filtered rows should not be part of the calculation.. Regards PO "Toppers" skrev i meddelandet ... Try: =SUM(IF(A2-(B2+C2)0,A2)) entered as an array formula, using Ctrl+Shift+Enter which will place curly brackets {} rounf the formula. And shouldn't the answer be 100? "PO" wrote: Hi, I posted a problem concerning the SUMIF formula yesterday. The problem has slighly changed. I want the SUMIF formula to add the values in KolA only if the sum of KolA-(KolB + KolC) is greater then 0. The comparison is to be down for each row. If I, for example, have the following numbers: Kol A Kol B KolC 100 25 30 60 40 30 25 20 15 In the above example the SUMIF formula should return 85. I need to use the SUMIF formula since the result should only be calculated for rows not hidden by the autofilter function. Regards PO |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Help with SUMIF function
Try:
=SUMPRODUCT(--(A2:A4-(B2:B4+C2:C4)0),--(A2:A4)) "PO" wrote: Hi, Thanks for your answer. You're right the result should be 100. The problem with your formula is that it doesn't work together with autofilter. I have to use SUMIF because filtered rows should not be part of the calculation.. Regards PO "Toppers" skrev i meddelandet ... Try: =SUM(IF(A2-(B2+C2)0,A2)) entered as an array formula, using Ctrl+Shift+Enter which will place curly brackets {} rounf the formula. And shouldn't the answer be 100? "PO" wrote: Hi, I posted a problem concerning the SUMIF formula yesterday. The problem has slighly changed. I want the SUMIF formula to add the values in KolA only if the sum of KolA-(KolB + KolC) is greater then 0. The comparison is to be down for each row. If I, for example, have the following numbers: Kol A Kol B KolC 100 25 30 60 40 30 25 20 15 In the above example the SUMIF formula should return 85. I need to use the SUMIF formula since the result should only be calculated for rows not hidden by the autofilter function. Regards PO |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Help with SUMIF function
=SUMPRODUCT(SUBTOTAL(3,OFFSET(A1,ROW($A$2:$A$10)-ROW($A$1),,1)),
--(A2:A10-(B2:B10+C2:C10)0),A2:A10) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "PO" <h wrote in message ... Hi, Thanks for your answer. You're right the result should be 100. The problem with your formula is that it doesn't work together with autofilter. I have to use SUMIF because filtered rows should not be part of the calculation.. Regards PO "Toppers" skrev i meddelandet ... Try: =SUM(IF(A2-(B2+C2)0,A2)) entered as an array formula, using Ctrl+Shift+Enter which will place curly brackets {} rounf the formula. And shouldn't the answer be 100? "PO" wrote: Hi, I posted a problem concerning the SUMIF formula yesterday. The problem has slighly changed. I want the SUMIF formula to add the values in KolA only if the sum of KolA-(KolB + KolC) is greater then 0. The comparison is to be down for each row. If I, for example, have the following numbers: Kol A Kol B KolC 100 25 30 60 40 30 25 20 15 In the above example the SUMIF formula should return 85. I need to use the SUMIF formula since the result should only be calculated for rows not hidden by the autofilter function. Regards PO |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Help with SUMIF function
I tried SUMIF with filtered data and filtered rows were included in the SUM
(unless I misunderstand your need). "Toppers" wrote: Try: =SUMPRODUCT(--(A2:A4-(B2:B4+C2:C4)0),--(A2:A4)) "PO" wrote: Hi, Thanks for your answer. You're right the result should be 100. The problem with your formula is that it doesn't work together with autofilter. I have to use SUMIF because filtered rows should not be part of the calculation.. Regards PO "Toppers" skrev i meddelandet ... Try: =SUM(IF(A2-(B2+C2)0,A2)) entered as an array formula, using Ctrl+Shift+Enter which will place curly brackets {} rounf the formula. And shouldn't the answer be 100? "PO" wrote: Hi, I posted a problem concerning the SUMIF formula yesterday. The problem has slighly changed. I want the SUMIF formula to add the values in KolA only if the sum of KolA-(KolB + KolC) is greater then 0. The comparison is to be down for each row. If I, for example, have the following numbers: Kol A Kol B KolC 100 25 30 60 40 30 25 20 15 In the above example the SUMIF formula should return 85. I need to use the SUMIF formula since the result should only be calculated for rows not hidden by the autofilter function. Regards PO |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Can I add and IF function to a SUMIF function? | Excel Worksheet Functions | |||
| SUMIF Function Inside SUMPRODUCT Function | Excel Worksheet Functions | |||
| SumIF function | Excel Discussion (Misc queries) | |||
| Sumif function with remote cell references | Excel Worksheet Functions | |||
| SUMIF function | Excel Worksheet Functions | |||
Linear Mode
