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

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 [email protected]> a écrit dans le message de
news: [email protected]...
> 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

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 [email protected]> a écrit dans le message de
> news: [email protected]...
> > 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
>
>
>

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" <[email protected]> a écrit dans le message de
news: [email protected]...
> 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 [email protected]> a écrit dans le message
>> de
>> news: [email protected]...
>> > 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
>>
>>
>>

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" <[email protected]> a écrit dans le message de
> news: [email protected]...
> > 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 [email protected]> a écrit dans le message
> >> de
> >> news: [email protected]...
> >> > 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
> >>
> >>
> >>
>
>
>

See my response.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Nigel RS" <[email protected]> wrote in message
news:[email protected]...
> 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 [email protected]> a écrit dans le message
de
> > news: [email protected]...
> > > 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
> >
> >
> >

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 [email protected]> wrote in message
news:[email protected]...
> 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

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 [email protected]> wrote in message
> news:[email protected]...
> > 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
>
>
>