VLOOKUP and SUMIF statement - any suggestions?

[Nick_86]

I have two tables in the same Sheet of a Workbook:

Table 1 (7 Columns (B-H), lots of rows):

COL B PRD Identifier	COL C CRD Identifier	COL D House ID	COL E Previous Reading Date	COL F Current Reading Date	COL G # Days	COL H KWH Usage
A8054132	A8054194	A8054	13-Feb-14	19-Apr-14	65	297
A8054194	A8054206	A8054	19-Apr-14	20-Jun-14	62	353
A838072	A8380104	A8380	07-Feb-14	10-Apr-14	62	492
A8380104	A8380136	A8380	10-Apr-14	13-Jun-14	64	504
A8730112	A8730154	A8730	11-Feb-14	15-Apr-14	63	738
A8730154	A8730176	A8730	15-Apr-14	17-Jun-14	63	599

<colgroup><col span="2"><col><col><col><col><col></colgroup><tbody>
</tbody>

Table 2 (4 Columns (K-N, lots of rows):

COL K HHD Identifier	COL L House ID	COL M Date	COL N KWH Usage
A265911	A2659	1/01/2015	11418
A265921	A2659	2/01/2015	11269
A265931	A2659	3/01/2015	12304
A265941	A2659	4/01/2015	26629
A265951	A2659	5/01/2015	35457
A265961	A2659	6/01/2015	12280

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>

So Table one has each row containing a House ID, the Previous Read Date (PRD) and Current Read Date (CRD) of the electricity meter along with the number of days (CRD-PRD) and the KWH usage during that period. For ease of identifying a row I've created the PRD Identifier and CRD Idendifier which is a combination of the House ID & PRD (dd/mm only) and House ID 7 CRD (dd/mm only) respectively. i.e. for the first row: PRD Identifier = House ID & PRD dd/mm = A8054 & 13/02 = A8054132.

All the dates in Table 1 are in the range January to July 2014 but different periods of time.

Table 2 contains the same houses (House IDs) but the daily usage for each from January to July 2015. The excerpt of Table 2 above continues until 31/7/15 then goes to the next House ID and does the same from 1/1/15 to 31/7/15 and so on. The HHD Identifier = House ID & Date (dd/mm only).

So using an Identifier from one Table I am able to find the correct row for the same House and date (as year isn't taken into account).

What I want to do is have some kind of VLOOKUP and SUMIF statement for each row of Table 1 (say in Column I) so I can locate the 'Previous Read Date' (using the PRD Identifier) and the Current Read Date (using the CRD Identifier) in Table 2 and return the SUM of all the KWH Usage associated with that House between those dates (from the PRD to the CRD) so I can directly compare the same periods of time: the period in 2014 from Table 1 to the same period in 2015 from Table 2.

Can someone please suggest a way I could do this?

 

[jono19872006]

If I have understood it correctly you should be able to do a sumproduct as such;

SUMPRODUCT(--(L:L=D2),--(E2<=M:M),--(F2>=M:M),N:N)

 

[123rickfear]

=SUMIFS(N:N,M:M,">="&E2,M:M,"<="&F2,L:L,D2) try that.

 

[Nick_86]

I think I can do it with a SUMPRODUCT, but using =SUMPRODUCT(--(L:L=D2),--(E2<=M:M),--(F2>=M:M),N:N) returns #VALUE!

jono, I think you are correct, but how can I do a SUMPRODUCT with the 3 following conditions which if satisfied =1, and then return the sum of the values in Column N for each of those rows?

Conditions:
If any value in a row in Column L (i.e. L:L) = D2
AND
If any value in a row in Column M (ie. M:M) >= E2
AND
If any value in a row in Column M (i.e. M:M) <= F2

where E2 and F2 are dates and all the values in Column M are dates.

If the above 3 conditions are satisfied then:
Take the value in that row in Column N (i.e. N:N) and add that value to all of other values in Column N for which their rows satisfy the conditions.

i.e. Lets say rows 17-20 in the table (L1:N65535) satisfies all the conditions, so L17:L20 all have the same value as the value in cell D2 AND M17:M20 all have a date value that's greater than or equal to E2's date but also less than or equal to F2's date. So then the SUMPRODUCT will return (1*1*15)+(1*1*20)+(1*1*22)+(1*1*17) corresponding to cells (L17*M17*N17)+(L18*M18*N18)+(L19*M19*N19)+(L20*M20*N20), where N17=15, N18=20, N19=22, N20=17. All the values/dates in L17:L20 and M17:M20 have satisfied the conditions so return the value 1. The value I want at the end of all of this example would then =15+20+22+17=74 (the sum of the values in N17-N20).

What does the -- mean/do in the above formula? Does it mean that the value of that variable become 1 if the statement is correct?

Any help would be much appreciated.