SUMIF formula with multiple criteria.

[whitaker]

My workbook contains two sheets. Sheet 1 is like a checkbook register. Sheet 2 is an expense summary . I need to summaries expenses by the person and by the date range or period. My header row on Sheet 1 is Row 7. Column C contains the “Date”. Column F contains the “Activity” ($ amount). Column J contains initials of the “Responsible Party”, ie “T W”. Question: From 03/01/2010 To 03/18/2010, what is the sum of “TW” expenses? And so on for each specific period. Summaries on Sheet 2. Enter query Date Range is Column A and B on Row 4.

 

[VoG]

Try like this

=SUMPRODUCT(--(Sheet1!C8:C100>="03/01/2010"+0),--(Sheet1!C8:C100<="03/18/2010"+0),--(Sheet1!J8:J100="TW"),Sheet1!F8:F100)

 

[T. Valko]

My workbook contains two sheets. Sheet 1 is like a checkbook register. Sheet 2 is an expense summary . I need to summaries expenses by the person and by the date range or period. My header row on Sheet 1 is Row 7. Column C contains the “Date”. Column F contains the “Activity” ($ amount). Column J contains initials of the “Responsible Party”, ie “T W”. Question: From 03/01/2010 To 03/18/2010, what is the sum of “TW” expenses? And so on for each specific period. Summaries on Sheet 2. Enter query Date Range is Column A and B on Row 4.

What version of Excel are you using?

 

[whitaker]

I'm using Office 2007.

 

[whitaker]

Got it. That gives me the info I need. Thank you so much.

 

[T. Valko]

I'm using Office 2007.

VoG's formula will work in any version of Excel but since you're using Excel 2007 you can use the SUMIFS function.

I'd use cells to hold all the criteria:

• A1 = lower date boundary = 3/1/2010
• B1 = upper date boundary = 3/18/2010
• C1 = TW

Then:

=SUMIFS(Sheet1!F8:F100,Sheet1!C8:C100,">="&A1,Sheet1!C8:C100,"<="&B1,Sheet1!J8:J100,C1)

 

[whitaker]

I saw that "SUMIFS" function but was unsuccessful in using it. I'll try your suggestion too. Thank you so much for the response.

 

[T. Valko]

I saw that "SUMIFS" function but was unsuccessful in using it. I'll try your suggestion too. Thank you so much for the response.

You're welcome!