Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

ComplicateD SUMIF Excel 2000 (2 conditions) 1

Status
Not open for further replies.

aarondewberry

IS-IT--Management
Jul 20, 2005
148
GB
All

I have two sheets:-
Code:
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

Code:
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.
 
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][glasses]Don't let the Diatribe...
talk you to death![tongue][/sup][sub]
[glasses]Just traded in my old subtlety...
for a NUANCE![tongue][/sub]
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top