• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 609
  • Last Modified:

SUMIF with a date criteria as well

I have a simple SUMIF which totals a column of cells based on a matching name:

Sheet2!B3     =SUMIF(Sheet1!A:A,A3,Sheet1!D:D)

This is for training courses which have a 'Course Date' (Sheet1!E:E) date cell. Sheet2!B1:M1 contains a heading of Months.

I need the SUMIF to total the values in Sheet1!D:D but also, only if the course date matches the same month as the heading in Sheet2!B1:B12.

I hope this, and the attached example spreadsheet (Excel 2003), makes some sense! So far I've tried SUMPRODUCT, SUMIF's, IF's and AND's, INDEX and MATCH, and I'm sure I've almost been there, but I can't find the right combination!

Thanks,
Lee

 EE-Example.xls
0
Lee Osborne
Asked:
Lee Osborne
1 Solution
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Hello,

you could do a pivot table with just a few clicks, or use sumproduct

=SUMPRODUCT(--(Sheet1!$A$2:$A$20=Sheet2!$A3),--(DATE(YEAR(Sheet1!$E$2:$E$20),MONTH(Sheet1!$E$2:$E$20),1)=Sheet2!B$1),Sheet1!$D$2:$D$20)

Attached is the sumproduct formula in action and a pivot table on Sheet3

cheers, teylyn
EE-Example.xls
0
 
Lee OsborneAuthor Commented:
Perfect, thanks very much!

I really should learn more about pivot tables, they really are good!

Lee
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now