Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMIF formula while filtering
wrote in message
... On May 8, 3:13 pm, "T. Valko" wrote: Try this: =SUMPRODUCT(SUBTOTAL(3,OFFSET(H14:H267,ROW(H14:H26 7)-ROW(H14),0,1)),--(K14:*K267=E2),V14:V267) -- Biff Microsoft Excel MVP wrote in message ... 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMIF formula while filtering
On May 8, 3:16*pm, "Bob Phillips" 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) wrote in message ... 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" |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMIF formula while filtering
I didn't give you a workbook.
-- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) wrote in message ... On May 8, 3:16 pm, "Bob Phillips" 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) wrote in message ... 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" |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMIF formula while filtering
On May 8, 3:53*pm, "T. Valko" wrote:
wrote in message ... On May 8, 3:13 pm, "T. Valko" wrote: Try this: =SUMPRODUCT(SUBTOTAL(3,OFFSET(H14:H267,ROW(H14:H26 7)-ROW(H14),0,1)),--(K14:**K267=E2),V14:V267) -- Biff Microsoft Excel MVP wrote in message ... 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- 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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMIF formula while filtering
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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMIF formula while filtering
On May 8, 6:40*pm, "T. Valko" 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! |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMIF formula while filtering
wrote in message
... On May 8, 6:40 pm, "T. Valko" 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
text filtering formula??? | Excel Discussion (Misc queries) | |||
Filtering Data With Formula | Excel Worksheet Functions | |||
Filtering on a formula | Excel Discussion (Misc queries) | |||
Filtering dates using a formula | Excel Worksheet Functions | |||
trouble filtering a list. Why isn't column filtering? | Excel Worksheet Functions |