|
Conditional SumIf
Hi All
I have been successfully using sumif and countif to group summary results. I also use subtotal to return sums and counts for autofiltered lists. How do I combine autofiltered lists into my sumif and countif statements? I could write the VBA code but would prefer a worksheet formula solution. Thanks |
Conditional SumIf
Use SUMPRODUCT with boolean conditions coerced to integer values:
SUMIF with 2 conditions: =SUMPRODUCT((A1:A999="Mike")*(B1:B999=10);C1:C999) COUNTIF: =SUMPRODUCT((A1:A999="Mike")*(B1:B999=10)) HTH -- AP "Nigel RS" <Nigel a écrit dans le message de news: ... Hi All I have been successfully using sumif and countif to group summary results. I also use subtotal to return sums and counts for autofiltered lists. How do I combine autofiltered lists into my sumif and countif statements? I could write the VBA code but would prefer a worksheet formula solution. Thanks |
Conditional SumIf
This is an example of counting with a filtered list
=SUMPRODUCT(SUBTOTAL(3,OFFSET(C1,ROW($C$2:$C$19)-ROW($C$1),,1)),--($C$2:$C$1 9="the value")) and summing =SUMPRODUCT(SUBTOTAL(3,OFFSET(C1,ROW($C$2:$C$19)-ROW($C$1),,1)), --($C$2:$C$19="IDFM"),$D$2:$D$19) -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Nigel RS" <Nigel wrote in message ... Hi All I have been successfully using sumif and countif to group summary results. I also use subtotal to return sums and counts for autofiltered lists. How do I combine autofiltered lists into my sumif and countif statements? I could write the VBA code but would prefer a worksheet formula solution. Thanks |
Conditional SumIf
hi Ardus
Thank you for the reply but I do not understand.....are you saying use the sumproduct function within the sumif function? I understnad their are 3 parameters for sumif. So with SUMIF(range,criteria,sum_range). My range to check is A10:A100, my condition is in cell A5, my range to sum is B10:B100. So I end up with =SUMIF(A10:A100,A5,B10:B100) If I apply an autofiliter the result does not change; hence my problem. Cheers Nigel "Ardus Petus" wrote: Use SUMPRODUCT with boolean conditions coerced to integer values: SUMIF with 2 conditions: =SUMPRODUCT((A1:A999="Mike")*(B1:B999=10);C1:C999) COUNTIF: =SUMPRODUCT((A1:A999="Mike")*(B1:B999=10)) HTH -- AP "Nigel RS" <Nigel a écrit dans le message de news: ... Hi All I have been successfully using sumif and countif to group summary results. I also use subtotal to return sums and counts for autofiltered lists. How do I combine autofiltered lists into my sumif and countif statements? I could write the VBA code but would prefer a worksheet formula solution. Thanks |
Conditional SumIf
See my response.
-- HTH Bob Phillips (remove nothere from email address if mailing direct) "Nigel RS" wrote in message ... hi Ardus Thank you for the reply but I do not understand.....are you saying use the sumproduct function within the sumif function? I understnad their are 3 parameters for sumif. So with SUMIF(range,criteria,sum_range). My range to check is A10:A100, my condition is in cell A5, my range to sum is B10:B100. So I end up with =SUMIF(A10:A100,A5,B10:B100) If I apply an autofiliter the result does not change; hence my problem. Cheers Nigel "Ardus Petus" wrote: Use SUMPRODUCT with boolean conditions coerced to integer values: SUMIF with 2 conditions: =SUMPRODUCT((A1:A999="Mike")*(B1:B999=10);C1:C999) COUNTIF: =SUMPRODUCT((A1:A999="Mike")*(B1:B999=10)) HTH -- AP "Nigel RS" <Nigel a écrit dans le message de news: ... Hi All I have been successfully using sumif and countif to group summary results. I also use subtotal to return sums and counts for autofiltered lists. How do I combine autofiltered lists into my sumif and countif statements? I could write the VBA code but would prefer a worksheet formula solution. Thanks |
Conditional SumIf
Hi Ardus
Thanks for the explanation, this works as you describe. But not sure how this helps the autofilter question. Unlike the subtotal function sumproducts acts on all rows (hidden or not). So although I can now have multiple conditions usiing sumproduct I still cannot limit it to filtered rows. Cheers Nigel "Ardus Petus" wrote: SUMIF deals with 1 condition only SUMPRODUCT gives the same result, but accepts several conditions. Your SUMIF becomes: =SUMPRODUCT((A10:A100=A5)*(B10:B100)) HTH -- AP "Nigel RS" a écrit dans le message de news: ... hi Ardus Thank you for the reply but I do not understand.....are you saying use the sumproduct function within the sumif function? I understnad their are 3 parameters for sumif. So with SUMIF(range,criteria,sum_range). My range to check is A10:A100, my condition is in cell A5, my range to sum is B10:B100. So I end up with =SUMIF(A10:A100,A5,B10:B100) If I apply an autofiliter the result does not change; hence my problem. Cheers Nigel "Ardus Petus" wrote: Use SUMPRODUCT with boolean conditions coerced to integer values: SUMIF with 2 conditions: =SUMPRODUCT((A1:A999="Mike")*(B1:B999=10);C1:C999) COUNTIF: =SUMPRODUCT((A1:A999="Mike")*(B1:B999=10)) HTH -- AP "Nigel RS" <Nigel a écrit dans le message de news: ... Hi All I have been successfully using sumif and countif to group summary results. I also use subtotal to return sums and counts for autofiltered lists. How do I combine autofiltered lists into my sumif and countif statements? I could write the VBA code but would prefer a worksheet formula solution. Thanks |
Conditional SumIf
Thanks Bob, for you solution. I still have not got my head around how it
works. In fact I have not been able so far to get it to work. I assume your list starts in column C (the filtered list) and column D the summation values. I have tried to replicate it but so far I get a total of zero! In the Offset function it shows row($c2$19) - row($c$1), does this not always return 1 ? why would you not use 1. In fact why use offset at all, or this something to do with the array values for the sumproduct function? Sorry to be a bit thick on this, but I truly am trying to understand it and how it works rather than just copy paste formulas. Cheers Nigel "Bob Phillips" wrote: This is an example of counting with a filtered list =SUMPRODUCT(SUBTOTAL(3,OFFSET(C1,ROW($C$2:$C$19)-ROW($C$1),,1)),--($C$2:$C$1 9="the value")) and summing =SUMPRODUCT(SUBTOTAL(3,OFFSET(C1,ROW($C$2:$C$19)-ROW($C$1),,1)), --($C$2:$C$19="IDFM"),$D$2:$D$19) -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Nigel RS" <Nigel wrote in message ... Hi All I have been successfully using sumif and countif to group summary results. I also use subtotal to return sums and counts for autofiltered lists. How do I combine autofiltered lists into my sumif and countif statements? I could write the VBA code but would prefer a worksheet formula solution. Thanks |
Conditional SumIf
Bob found this posting of yours,
Fantastic, wadda guy! "Bob Phillips" wrote: This is an example of counting with a filtered list =SUMPRODUCT(SUBTOTAL(3,OFFSET(C1,ROW($C$2:$C$19)-ROW($C$1),,1)),--($C$2:$C$1 9="the value")) and summing =SUMPRODUCT(SUBTOTAL(3,OFFSET(C1,ROW($C$2:$C$19)-ROW($C$1),,1)), --($C$2:$C$19="IDFM"),$D$2:$D$19) -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Nigel RS" <Nigel wrote in message ... Hi All I have been successfully using sumif and countif to group summary results. I also use subtotal to return sums and counts for autofiltered lists. How do I combine autofiltered lists into my sumif and countif statements? I could write the VBA code but would prefer a worksheet formula solution. Thanks |
| All times are GMT +1. The time now is 10:06 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com