SUMIF formula while filtering

[djc276]

I have the formula =SUMIF(K14:K267,E2,V14:V267) but want the number to
only reflect when I filter by data in column H. Any ideas?

 

[T. Valko]

Try this:

=SUMPRODUCT(SUBTOTAL(3,OFFSET(H14:H267,ROW(H14:H267)-ROW(H14),0,1)),--(K14:K267=E2),V14:V267)

 

[Bob Phillips]

=SUMPRODUCT(SUBTOTAL(9,OFFSET(V13,ROW(V14:V267)-ROW(V13),,1)),--(K14:K267=E2))


--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[djc276]

Try this:

=SUMPRODUCT(SUBTOTAL(3,OFFSET(H14:H267,ROW(H14:H267)-ROW(H14),0,1)),--(K14:­K267=E2),V14:V267)

--
Biff
Microsoft Excel MVP






- Show quoted text -

I get "0" when I do that... does it help to know that the data in
column "H" is text?

 

[T. Valko]

Try this:

=SUMPRODUCT(SUBTOTAL(3,OFFSET(H14:H267,ROW(H14:H267)-ROW(H14),0,1)),--(K14:­K267=E2),V14:V267)

--
Biff
Microsoft Excel MVP






- Show quoted text -

I get "0" when I do that... does it help to know that the data in
column "H" is text?
Hmmm...

It works for me.

Here's a small sample file that demonstrates this:

xSumifFilter.xls 17kb

http://cjoint.com/?fjaXQiGAoG

 

[djc276]

=SUMPRODUCT(SUBTOTAL(9,OFFSET(V13,ROW(V14:V267)-ROW(V13),,1)),--(K14:K267=E­2))

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)






- Show quoted text -

i'm not sure what the problem is either. I even copied from your
workbook to mine & tripple checked that I had all the columns
correct... when I put it on mind it's coming up with "0"

 

[Bob Phillips]

I didn't give you a workbook.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

=SUMPRODUCT(SUBTOTAL(9,OFFSET(V13,ROW(V14:V267)-ROW(V13),,1)),--(K14:K267=E­2))

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)






- Show quoted text -

i'm not sure what the problem is either. I even copied from your
workbook to mine & tripple checked that I had all the columns
correct... when I put it on mind it's coming up with "0"

 

[djc276]

I get "0" when I do that... does it help to know that the data in
column "H" is text?



Hmmm...

It works for me.

Here's a small sample file that demonstrates this:

xSumifFilter.xls  17kb

http://cjoint.com/?fjaXQiGAoG

--
Biff
Microsoft Excel MVP- Hide quoted text -

- Show quoted text -

I think there is something else going on.... i don't think excel is
counting all the numbers in column v. I did get it to work but it's
not adding up to the right number

 

[T. Valko]

I think there is something else going on.... i don't think excel is

counting all the numbers in column v. I did get it to work but it's
not adding up to the right number

Are you sure column V contains all numeric numbers?

If there are no empty cells in the range and every cell contains a numeric
number then this formula shoud return 254:

=COUNT(V14:V267)

 

[djc276]

Are you sure column V contains all numeric numbers?

If there are no empty cells in the range and every cell contains a numeric
number then this formula shoud return 254:

=COUNT(V14:V267)

i got it, for some reason excel wasn't recognizing column v as having
numbers. I had to go through & each line & click inside the cell then
click enter before the formlua would work.... thank you for your help!

 

[T. Valko]

Are you sure column V contains all numeric numbers?

If there are no empty cells in the range and every cell contains a numeric
number then this formula shoud return 254:

=COUNT(V14:V267)

Glad you got it straightened out. Thanks for the feedback!