Monthly Progression

Hi all!

Well, here's the thing (see attached example please): Given a revenue report I export from our CRM, I need to calculate the progression of our Marketing Campaigns, based on: a) the date the deal is created in and b) the SourceCode (identifier) of each campaign. I built a table so I can later make a nice graph.

I know it shouldn't be hard, but I´m still new to excel, so can you guys pls help?

Here are some example of what I need (again, see attached file for a more clear example)

In C23 = The SUM of column D, where the SourceCode is W1 and creation date within the month of May.
In D23 = Same thing but for the month of June

I'm using Excel 2007 if that helps..

Thanks!!

Ale

First thing -- change your values in row 21 to be dates... eg C21 should be entered as May 1st, D21 Jun 1st etc.... you can format row C21 as "mmm" to show only the month name but having dates in these cells will make your life a lot easier.

Once the above is done you can use a SUMPRODUCT

C23: =SUMPRODUCT(--($E$4:$E$18=$B23),--(($C$4:$C$18-DAY($C$4:$C$18))+1=C$21),$D$4:$D$18)

the above can then be copied across the range.

In XL07 you also have SUMIFS but to use this you would need to add another field to your table which holds the month in a way you can cross check with the date in row 21.

Hi there!! I´ll try your solution first, thanks for answering.

A question though: what if I would need to match a number of source codes to a list? That is, instead of checking if "W1" is present, the function should check if any of the values in a list called W1 are present (i.e.: W1.1, W1.2, W1.3, etc)
?

Should I use a match formula there?

Thanks

Alejandro

Post an example of what you have in mind?

Hi! Sorry for the delay, I was out of office all day.

I´m attaching the modified example. I need to do the same as commented on the original post, though now you will see that W1, W2, W3 and W4 are groups of different SourceCodes.

So now:

In C23 = The SUM of column D, where the SourceCode is any of the ones in the W1 group and creation date within the month of May.

Hope you can help

Thanks again!

Ale

First things first: never use merged cells if you need to use their values in formulae. They are nothing but trouble.
You can use them to make your sheet look nice, but it's best to keep it at that

To solve your problem I added a helper column to your table so that the Sourcecode values (W1...) can be added...
Starting from there use my previous post to creat your PT

Not meant to be an improvement of arthurbr's use of PT (which is most likely the better solution for you pending on usage of data) but meant to illustrate how you could extend Sumproduct for your scenario see attached.

For some reason the wrong file was uploaded... grr.

See attached.