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