SUMIF formula while filtering

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
Thu May 08 17:13:11 CDT 2008

Try this:

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

--
Biff
Microsoft Excel MVP


<djc276@gmail.com> wrote in message
news:6853192c-ea5e-40e1-bb89-665911456bf3@m44g2000hsc.googlegroups.com...
>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?

Bob
Thu May 08 17:16:36 CDT 2008

=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@gmail.com> wrote in message
news:6853192c-ea5e-40e1-bb89-665911456bf3@m44g2000hsc.googlegroups.com...
>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?

djc276
Thu May 08 17:29:14 CDT 2008

On May 8, 3:13=A0pm, "T. Valko" <biffinp...@comcast.net> wrote:
> Try this:
>
> =3DSUMPRODUCT(SUBTOTAL(3,OFFSET(H14:H267,ROW(H14:H267)-ROW(H14),0,1)),--(K=
14:=ADK267=3DE2),V14:V267)
>
> --
> Biff
> Microsoft Excel MVP
>
> <djc...@gmail.com> wrote in message
>
> news:6853192c-ea5e-40e1-bb89-665911456bf3@m44g2000hsc.googlegroups.com...
>
>
>
> >I have the formula =3DSUMIF(K14:K267,E2,V14:V267) but want the number to
> > only reflect when I filter by data in column H. Any ideas?- Hide quoted =
text -
>
> - Show quoted text -

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

T
Thu May 08 17:53:10 CDT 2008

<djc276@gmail.com> wrote in message
news:1fc5c1e9-9537-45a5-a326-a177ecd9f5a8@t12g2000prg.googlegroups.com...
On May 8, 3:13 pm, "T. Valko" <biffinp...@comcast.net> wrote:
> Try this:
>
> =SUMPRODUCT(SUBTOTAL(3,OFFSET(H14:H267,ROW(H14:H267)-ROW(H14),0,1)),--(K14:­K267=E2),V14:V267)
>
> --
> Biff
> Microsoft Excel MVP
>
> <djc...@gmail.com> wrote in message
>
> news:6853192c-ea5e-40e1-bb89-665911456bf3@m44g2000hsc.googlegroups.com...
>
>
>
> >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?- Hide quoted
> > text -
>
> - 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

--
Biff
Microsoft Excel MVP

djc276
Thu May 08 18:18:55 CDT 2008

On May 8, 3:16=A0pm, "Bob Phillips" <bob....@somewhere.com> wrote:
> =3DSUMPRODUCT(SUBTOTAL(9,OFFSET(V13,ROW(V14:V267)-ROW(V13),,1)),--(K14:K26=
7=3DE=AD2))
>
> --
> ---
> HTH
>
> Bob
>
> (there's no email, no snail mail, but somewhere should be gmail in my addy=
)
>
> <djc...@gmail.com> wrote in message
>
> news:6853192c-ea5e-40e1-bb89-665911456bf3@m44g2000hsc.googlegroups.com...
>
>
>
> >I have the formula =3DSUMIF(K14:K267,E2,V14:V267) but want the number to
> > only reflect when I filter by data in column H. Any ideas?- Hide quoted =
text -
>
> - 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
Thu May 08 18:22:28 CDT 2008

I didn't give you a workbook.

--
HTH

Bob

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

<djc276@gmail.com> wrote in message
news:49fa3fde-f8f1-4073-adb0-4a13052abdd4@f63g2000hsf.googlegroups.com...
On May 8, 3:16 pm, "Bob Phillips" <bob....@somewhere.com> wrote:
> =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)
>
> <djc...@gmail.com> wrote in message
>
> news:6853192c-ea5e-40e1-bb89-665911456bf3@m44g2000hsc.googlegroups.com...
>
>
>
> >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?- Hide quoted
> > text -
>
> - 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
Thu May 08 18:34:07 CDT 2008

On May 8, 3:53=A0pm, "T. Valko" <biffinp...@comcast.net> wrote:
> <djc...@gmail.com> wrote in message
>
> news:1fc5c1e9-9537-45a5-a326-a177ecd9f5a8@t12g2000prg.googlegroups.com...
> On May 8, 3:13 pm, "T. Valko" <biffinp...@comcast.net> wrote:
>
>
>
>
>
> > Try this:
>
> > =3DSUMPRODUCT(SUBTOTAL(3,OFFSET(H14:H267,ROW(H14:H267)-ROW(H14),0,1)),--=
(K14:=AD=ADK267=3DE2),V14:V267)
>
> > --
> > Biff
> > Microsoft Excel MVP
>
> > <djc...@gmail.com> wrote in message
>
> >news:6853192c-ea5e-40e1-bb89-665911456bf3@m44g2000hsc.googlegroups.com...=

>
> > >I have the formula =3DSUMIF(K14:K267,E2,V14:V267) but want the number t=
o
> > > only reflect when I filter by data in column H. Any ideas?- Hide quote=
d
> > > text -
>
> > - 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 =A017kb
>
> 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
Thu May 08 20:40:02 CDT 2008

>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)

--
Biff
Microsoft Excel MVP

djc276
Fri May 09 08:25:07 CDT 2008

On May 8, 6:40=A0pm, "T. Valko" <biffinp...@comcast.net> wrote:
> >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:
>
> =3DCOUNT(V14:V267)
>
> --
> Biff
> Microsoft Excel MVP

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
Fri May 09 12:17:02 CDT 2008

<djc276@gmail.com> wrote in message
news:f170f28c-3560-470f-8b08-01fdf334c747@k1g2000prb.googlegroups.com...
On May 8, 6:40 pm, "T. Valko" <biffinp...@comcast.net> wrote:
> >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)
>
> --
> Biff
> Microsoft Excel MVP

>>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!

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

--
Biff
Microsoft Excel MVP