sumif help needed

[Bob_ipc]

Hello all, got a major brain fart situation. I am doing some work to an existing spreadsheet and there is a formula =SUMIF(C5:C938,B940,J5:J938). I need to be able to use a filter but I cannot figure it out using offset, please help

Thank you.

 

[Aryatect]

Hi,

Below should work:

Book1
	A	B
1	Name	A
2	Name 1	140
3	Name 2	135
4	Name 1	188
5	Name 4	138
6	Name 1	120
7	Name 6	150
8	Name 7	104
9	Name 1	109
10	Name 9	134
11	Name 10	132
12	Name 1	146
13	Name 12	120
14	Name 13	132
15	Name 1	140
16
17
18
19	Name 1	843
Sheet5

Cell Formulas
Range	Formula
B19	{=SUM(IF($A$2:$A$15=A19,SUBTOTAL(109,OFFSET(B2,ROW(B2:B15)-2,))))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Aryatect]

Another Non-Array option:

=SUMPRODUCT(--($A$2:$A$15=A19),SUBTOTAL(109,OFFSET(B2,ROW(B2:B15)-2,)))

 

[Bob_ipc]

Hello and thank you for your reply, I am still not getting the result I'm looking for, I used =SUM(IF($C$5:$C$938=B940,SUBTOTAL(109,OFFSET(J5,ROW(J5:J938)-2,)))) I did not filter anything and the sum it came up with was 581 when the total is actually 1933. Same results when using =SUMPRODUCT(--($C$5:$C$938=B940),SUBTOTAL(109,OFFSET(J5,ROW(J5:J938)-2,))). I should mention that c5:c938 is using words as references not numbers.

Thanks

 

[Aryatect]

Hi Try this

=SUMPRODUCT(--($C$5:$C$938=B940),SUBTOTAL(109,OFFSET(J5,ROW($J$5:$J$938)-ROW($J$5),)))

if this does not work, can you share some of the dummy data, will take a look at that.

 

[Bob_ipc]

HEY!!! that works great!! thank you! now I only need to enter it in about 100 times haha, turns out it does not carry over or down very well when B column needs to stay constant and array columns change. Thanks again!

 

[Aryatect]

Glad could help!

Instead of B90 use $B90 and it should make it usable across.

 

[Bob_ipc]

BUDDY!!!! you just saved me so much time!! Thanks!!!!

 

[Aryatect]

You are most welcome