ComplicateD SUMIF Excel 2000 (2 conditions) 1

[aarondewberry]

All

I have two sheets:-

Sheet 1
A              B          C              D
Trans Date     BG         Cut Off Date	 Visits
29/10/2008	1	  08/11/2008	
30/10/2008	2	  09/11/2008

Sheet 2
A               B       C
Trans Date	BG	Results
30/10/2008	1	420
31/10/2008	1	2,841
01/11/2008	1	2,499
03/11/2008	1	39
04/11/2008	1	137
05/11/2008	1	236
06/11/2008	1	67
07/11/2008	1	28
27/11/2008	1	94
02/12/2008	1	2,609
31/10/2008	2	493
01/11/2008	2	2,345

What I want to do is write a formula, in the visits field in Sheet 1, to sum the results in sheet 2 where the dates are between sheet1 Transdate and sheet1 cut off date and where the BG's are the same.

So in the example above for BG:1 the visits would be 6267.

Hope this makes sense.

 

[SkipVought]

hi,

Check out the SUMPRODUCT function
[tt]
=SUMPRODUCT((Trans_Date>=A2)*(Trans_Date<=C2)*(BG=B2)*(Results))
[/tt]
I use named ranges in your sheet2 table.

Got the result your expected.

Skip,
[sup]Don't let the Diatribe...
talk you to death![/sup][sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[aarondewberry]

Mr Skip you are a legend.

Have a star!