Using VLOOKUP and "AND" together

[chocoman]

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

 

[Pete_UK]

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

 

[chocoman]

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]

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

 

[chocoman]

Thanks again Pete. I tallied it by week, period and year. It worked
fantabulously!!!

Cheers

 

[Pete_UK]

Good to hear that, Mike - thanks for feeding back and letting me know.

Pete