Conditional SumIf

[Nigel RS]

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

 

[Ardus Petus]

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

 

[Bob Phillips]

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]

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]

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

 

[Bob Phillips]

See my response.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Nigel RS]

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

 

[Nigel RS]

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

 

[LaDdIe]

Bob found this posting of yours,
Fantastic, wadda guy!