|
Using 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 |
Using VLOOKUP and "AND" together
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 wrote: 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 |
Using VLOOKUP and "AND" together
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? "Pete_UK" wrote: 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 wrote: 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 |
Using VLOOKUP and "AND" together
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 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? *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? "Pete_UK" wrote: 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 wrote: 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- Hide quoted text - - Show quoted text - |
Using VLOOKUP and "AND" together
Thanks again Pete. I tallied it by week, period and year. It worked
fantabulously!!! Cheers "Pete_UK" wrote: 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 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? 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? "Pete_UK" wrote: 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 wrote: 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- Hide quoted text - - Show quoted text - |
Using VLOOKUP and "AND" together
Good to hear that, Mike - thanks for feeding back and letting me know.
Pete On May 17, 8:11*pm, chocoman wrote: Thanks again Pete. I tallied it by week, period and year. It worked fantabulously!!! Cheers |
| All times are GMT +1. The time now is 12:52 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com