Ignoring filtered cells with SUMIF

[NICKRENN]

Hi

I realise this has been answered before but i don't understand any of the solutions so can't apply it to my problem.

everywhere i look i find something like this as an example solution:

[FONT=&quot]=SUMPRODUCT(--(SUBTOTAL(3,OFFSET(INDEX($A$1:$A$100,1,1),ROW($A$1:$A$100)-ROW(INDEX($A$1:$A$100,1,1)),0))=1),--($B$1:$B$100="North"),$A$1:$A$10[/FONT][FONT=&quot]0[/FONT][FONT=Segoe UI, Arial, sans-serif])[/FONT]

[FONT=Segoe UI, Arial, sans-serif]I don't understand this formula. [/FONT]

[FONT=Segoe UI, Arial, sans-serif]what i am trying to do is simple in is essence, seems harder in practice [/FONT]

[FONT=Segoe UI, Arial, sans-serif]I want to sumif the number in Column i is below 4500.1 - but i have 2000+ filtered rows out of around 4000 rows in total, i only want the sum of those visible to me.

Can someone, a: help me with a formula, b; explain to me how the formula they suggest, assuming it is something similar to the one above actually works.

Thanks very much.

Nick [/FONT]

 

[NICKRENN]

If anyone has any help, i need to get this solved for a deadline i have in 2hrs.

Please if you have any assistance to my issue would be much appreciated.

Many thanks

Nick

 

[NARAYANK991]

Hi ,

See if this works.

=SUMPRODUCT((SUBTOTAL(3,OFFSET(INDEX($I$5:$I$300,1,1),ROW($I$5:$I$300)-ROW(INDEX($I$5:$I$300,1,1)),0))=1) * ($I$5:$I$300<=4500.1),$I$5:$I$300)

How this works is as follows :

The last section , colored red , is the range which will be summed up if certain conditions are met.

The conditions to be met are a combination of the first two sections , where the first section , colored brown , is the one which looks at only filtered cells.

The second section colored blue is the filter that you have introduced.

Thus considering just one column of data , suppose you have amounts ranging from 1000 to 10000. Suppose you set a filter for all amounts greater than 2000 ; the first section will generate an array of 0s and 1s , 0 where the amount is less than 2000 , and 1 everywhere else.

The second section will also generate an array of values , 0 where the amount is greater than 4500.1 , and 1 everywhere else.

Multiplying these two arrays will generate a result array , again of 0s and 1s , 0 where either array had a 0 , and 1 only in those locations where both arrays had a 1.

Based on where this result array has 1s , only those elements of the range in the third section will be summed up.

Thus the output will be a summation of those amounts which fall between 2000 and 4500.1

 

[Aladin Akyurek]

Should be:

=SUMPRODUCT(SUBTOTAL(9,OFFSET($I$2),ROW($I$2:$I$4000)-ROW($I$2),0,1)),--($I$2:$I$4000 < 4500.1))

 

[NICKRENN]

WINNING! thanks so much. just changed the cell references for my table and boom, there it is.

Thanks so much

 

[Aladin Akyurek]

WINNING! thanks so much. just changed the cell references for my table and boom, there it is.

Thanks so much

Small edit though in case you were adopting post #4 ...

=SUMPRODUCT(SUBTOTAL(9,OFFSET($I$2,ROW($I$2:$I$4000)-ROW($I$2),0,1)),--($I$2:$I$4000 < 4500.1))

​