 |
 | 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 2008Using VLOOKUP and "AND" together
I have a worksheet that looks like this: A B C D E F G .............................. Date --- Line --- --- --- Value 01-Jan-08 ---- MR1 200 01-Jan-08 ---- MR1 100 01-Jan-08 ---- MR2 50 01-Jan-08 ---- MR2 75 01-Jan-08 ---- MR2 125 This is a very large database with four different (C column) types per date and each type may have 1-6 entries. I want to be able to lookup the date and sum the values for each C column type. Can anyone help please? N.B. The columns which are blank have info, however I don't need that info right now. Mike Put the date of interest in, say, i2, and this formula in J2: =SUMPRODUCT((A$1:A$1000=i$2)*(C$1:C$1000="MR1")*(G$1:G$1000)) to give you a sum of the MR1 entries for that date. I've assumed that you have 1000 rows of data, but adjust this if you have more. You can copy the formula down and just change the "MR1" bit as appropriate for the other Line values, or you could put the line values in H2 to H7, say, and change the formula to this: =SUMPRODUCT((A$1:A$1000=i$2)*(C$1:C$1000=H2)*(G$1:G$1000)) and then copy down. Hope this helps. Pete On May 16, 7:42 pm, chocoman <choco...@discussions.microsoft.com> wrote: > I have a worksheet that looks like this: > [quoted text clipped - 17 lines] > right now. > Mike Thank you Pete. It worked perfectly. Now, I would like to tally the same results per week. Is it possible to nest a total in one formula? Date --- Line --- --- --- Value 01-Jan-08 ---- MR1 200 01-Jan-08 ---- MR1 100 01-Jan-08 ---- MR2 50 01-Jan-08 ---- MR2 75 01-Jan-08 ---- MR2 125 02-Jan-08 ---- MR1 50 02-Jan-08 ---- MR2 75 02-Jan-08 ---- MR2 125 I want to look up 01-Jan-08 to 31-Jan-08 for MR2, it should give me: 50+75+125+75+125=450 Is it possible or am I dreaming? > Put the date of interest in, say, i2, and this formula in J2: > [quoted text clipped - 37 lines] > > right now. > > Mike You say you want to do it for a week, and then say you want it for January. If the latter, then you can do this: =SUMPRODUCT((MONTH(A$1:A$1000)=MONTH(i$2))*(C$1:C$1000=H2)*(G$1:G $1000)) i.e. for the month of the date in i2, or if you want to do it for one week (7 days) from the date in i2, you can do it this way: =SUMPRODUCT((A$1:A$1000>=i$2)*(A$1:A$1000<=i$2+7)*(C$1:C$1000=H2)*(G $1:G$1000)) Hope this helps. Pete On May 17, 12:27 pm, chocoman <choco...@discussions.microsoft.com> wrote: > Thank you Pete. It worked perfectly. Now, I would like to tally the same > results per week. Is it possible to nest a total in one formula? [quoted text clipped - 57 lines] > > - Show quoted text - Thanks again Pete. I tallied it by week, period and year. It worked fantabulously!!! Cheers > You say you want to do it for a week, and then say you want it for > January. If the latter, then you can do this: [quoted text clipped - 75 lines] > > > > - Show quoted text - Good to hear that, Mike - thanks for feeding back and letting me know. Pete On May 17, 8:11 pm, chocoman <choco...@discussions.microsoft.com> wrote: > Thanks again Pete. I tallied it by week, period and year. It worked > fantabulously!!! > > Cheers |
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.