SUMIF + MATCH?

[MICRO]

Hi Mr Excel,

I need to Sum a column with 2 criterias:
date & actuals

In one month, some of the invoices might already be paid,(ACTUALS) and some remains to be paid later that month.(FORECAST)


Please see attached file.

Please help.
Thanks!!

[prinda123]

Hi
I won't answer your question, but just direct you to the answer (you'll need to apply the solution below to your problem, but its pretty straight forward):

http://www.youtube.com/watch?v=EQcx6YUZGGg

If the above doesn't work you may be better to use VBA for the solution and posting the question there.
Regards
David

[MICRO]

Thanks David, I will check it out!!

[Don]

There is a pretty simple way to do this with the sumifs() function. First though, it is always a good idea to enter dates as dates. When you do that, you can use a myriad of date functions on the dates that allow you to get things like the day, year, and month from any date.

That said, here is all you would need to do given your data:

• In column D, enter this formula =LEFT(A1,2) and copy it down.
• In cell F2, enter this formula =SUMIFS(B:B,C:C,$F$1,D:D,LEFT(E2,2)) and copy it down.
• In cell G2, enter this formula =SUMIFS(B:B,C:C,$G$1,D:D,LEFT(E2,2)) and copy it down.

If you follow these steps, you should get the desired result.

hope this helps