 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Worksheet Functions / May 2008SUMIF 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? Try this: =SUMPRODUCT(SUBTOTAL(3,OFFSET(H14:H267,ROW(H14:H267)-ROW(H14),0,1)),--(K14:K267=E2),V14:V267)  SignatureBiff Microsoft Excel MVP >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? > Try this: > [quoted text clipped - 12 lines] > > - Show quoted text - I get "0" when I do that... does it help to know that the data in column "H" is text? On May 8, 3:13 pm, "T. Valko" <biffinp...@comcast.net> wrote: > Try this: > [quoted text clipped - 13 lines] > > - 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 > <djc...@gmail.com> wrote in message > [quoted text clipped - 37 lines] > > - 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 >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) SignatureBiff Microsoft Excel MVP > >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 [quoted text clipped - 10 lines] > 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! 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 [quoted text clipped - 10 lines] > 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! SignatureBiff Microsoft Excel MVP =SUMPRODUCT(SUBTOTAL(9,OFFSET(V13,ROW(V14:V267)-ROW(V13),,1)),--(K14:K267=E2)) Signature--- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) >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? > =SUMPRODUCT(SUBTOTAL(9,OFFSET(V13,ROW(V14:V267)-ROW(V13),,1)),--(K14:K267=E2)) > [quoted text clipped - 14 lines] > > - 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" I didn't give you a workbook. SignatureHTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) 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=E2)) > [quoted text clipped - 16 lines] > > - 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" |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.