Analyse sales by month of proposal

**tabira** · 04-30-2010, 07:11 AM

Dear All

I'm struggling! I need to analyse sales by the month when the original proposal was made. I have attached a simplified version of a more complex spreadsheet with only the relevant sections. The proposal dates are in column A in format "15/05". I need to find out:
1. how many proposal types (column B) were made in March, April, June etc....
2. how many sales (column C) resulted from proposals made in March, April June etc....
3. how much revenue (column D) was generated from proposals made in March, April June...

I already have a formula to calculate the total numbner of proposals by month whereby L10 is 01/03 and M10 is 31/03:

=SUMPRODUCT((A3:A999>=L10)*(A3:A999<=M10))

However I'm really struggling to adapt this formula to include an extra criteria like "type", "sale" or "revenue" as in points 1-3 above. (please note that the excel file may show some words in French - e.g. "mars" instead of "march")

Thank you for your help!

**DonkeyOte** · 04-30-2010, 07:24 AM

Given your use of XL2007 I would suggest you look at COUNTIFS & SUMIFS functions in XL Help in pref. to SUMPRODUCT given they are more efficient.

eg:

Please Login or Register  to view this content.

Only use SUMPRODUCT if backwards compatibility is an issue.

If you were to store the 1st of month date in the headers of the table you could simplify further.

**tabira** · 04-30-2010, 08:48 AM

Dear DonkeyOte

I can't thank you enough for your help. I've wasted a week at work trying to crack this on my own and you gave me the perfect and complete answer in a few minutes. It works like a dream.

God bless you !!!

tabira