Sumif visible cells only

[ccampb18]

I know there are several threads on this already but the sumproduct, subtotal, offset, index row functions confused me. So I am hoping someone can break it down for my specific example.

Here is my simple sumif formula

sumif($b$2:$N:$2796,A2,$N$2$N$2796)

Basically it is saying looking at this table(b2:n2796, match this DC (A2), and sum its qty in this range(N2:N276). Duh! But since I have other qualifiers I am needing to filter out some of these and I want to only sumif the visible cells.

if sumproduct is the answer, can someone break it down what each function in the formula is doing? I struggle to understand why it is all necessary.

Thanks!

 

[ccampb18]

Bump

 

[barry houdini]

Generically to sum sumrange based on a match in criteriarange.....but only for visible rows you can use this formula:

=SUMPRODUCT((criteriarange=criteria)+0,SUBTOTAL(109,OFFSET(sumrange,ROW(sumrange)-MIN(ROW(sumrange)),0,1,1)))

The first part (criteriarange=criteria)+0 just checks the criteria for each row and returns 1 for a match or 0

OFFSET returns an "array of ranges" with each range in this case being a single cell from the sum range. SUBTOTAL can process that and with the sum function (109) gives the "sum" (i.e. the value) of each cell, only when visible.

SUMPRODUCT then multiplies the two ranges and sums the result, effectively giving you the sum of visible rows where the criteria matches

In your case that would become this specific formula, I think

=SUMPRODUCT(($B$2:$B$2796=A2)+0,SUBTOTAL(109,OFFSET($N$2:$N$2796,ROW($N$2:$N$2796)-MIN(ROW($N$2:$N$2796)),0,1,1)))

There's an explanation here although that's a count, not a sum......but the only real difference is changing 103 in SUBTOTAL (COUNTA) to 109 (SUM)

 

[ccampb18]

Houdini - Fantastic!

I appreciate the thoroughness. It all makes sense now and the formula works beautifully!