more complex than countif ???

[kgkev]

I'm looking to create a monthly sales report based on brand and month.

I have done it in the past using a load of if statements checking for monthly totals and then adding up the coloumn.

But there must be an easier way.

I have 4 brands. so In january I want to find the sum of the quotes based on each brand.

'A' contains the date
'J' contains the brand - Say A, B C or D
'M' contains the quote value

I want to firstly count the number of enquiries per brand per mont. (these are all entries without a qoute value.

Then I want to count the number of quotes per brand month (these are the entries with a quote value)

The I want to find the total value of the quotes per brand . per month.

any help would be greatly appreciated.

 

[Richard Schollar]

Hi

Seems like you have a good data structure which would facilitate using a Pivot Table - this would provide you with the summaries you want?

 

[kgkev]

I'm creating the report for someone else.

At the moment they click a button and it gets all the data from several spread sheets.

I want them to then click the reports tab and have a load of tables - 1 for each month. all filled in with live data. and they can just print which ever month they want.

 

[DonkeyOte]

assuming you had your *results* on a new sheet with

brands (A to D) in A2 to A5
month in A1 (eg 01/04/08)
in B1 = "Enquiries"
in C1 = "Quotes"
in D1 = "Quote Value"

and your source data was on sheet "DATA" and was in 100 rows from row 2 to 101

formula for B2 on your results tab would be

=SUMPRODUCT(--(TEXT(DATA!$A$2:$A$101,"YYYY-MM")=TEXT($A$1,"YYYY-MM")),--(DATA!$J$2:$J$101=$A2),--(ISBLANK(DATA!$M$2:$M$101)))

formula for C2 on your results tab would be

=SUMPRODUCT(--(TEXT(DATA!$A$2:$A$101,"YYYY-MM")=TEXT($A$1,"YYYY-MM")),--(DATA!$J$2:$J$101=$A2),--(ISNUMBER(DATA!$M$2:$M$101)))

formula for D2 on your results tab would be

=SUMPRODUCT(--(TEXT(DATA!$A$2:$A$101,"YYYY-MM")=TEXT($A$1,"YYYY-MM")),--(DATA!$J$2:$J$101=$A2),--(DATA!$M$2:$M$101))

these formulae could be copied down for each brand row.

However, pending number of brands in your real scenario etc a PT is certainly the better option.

 

[kgkev]

Thanks - Thats great

I actually did it with brands along the top so ended up with

=SUMPRODUCT(--(TEXT(Master!$A$4:$A$500,"YYYY-MM")=TEXT($A$1,"YYYY-MM")),--(Master!$J$4:$J$500=B$1),--(Master!$M$4:$M$500))

2 last coloumns to fill in now.

Average number of quotes so far this year per month and per brand

Average value of quotes so far this year per month and per brand.

 

[DonkeyOte]

well assuming by year you're referring to say calendar year (ie Jan 1 - Dec 31) then you can just adjust your date criteria to match along the lines of TEXT(date,"YYYY") as opposed to YYYY-MM as you no longer need to filter on month... this assumes of course your data will only include YTD data and not "forecast" items in future months...

In terms of the denominator to ascertain average - if you are mid way through the month do you count the current month as 1, 0 or .5 etc ?

 

[kgkev]

reports will only be created at the end of the month.

so only complete months need to be concidered

 

[DonkeyOte]

in which case you can use

=SUMPRODUCT(...)/MONTH($A$1)

to get your averages.

(again assumes calendar year as *year* rather than say Apr1-Mar31)

 

[kgkev]

spot on thanks for you help.

 

[kgkev]

After some testing this report doesn't seem to be giving the right results

these are the formula I have used

Enquiries this month
=SUMPRODUCT(--(TEXT(Master!$A$4:$A$1754,"YYYY-MM")=TEXT($B34,"YYYY-MM")),--(Master!$J$4:$J$1754=D34),--(ISBLANK(Master!$N$4:$N$1754)))
Seems to work

Quote this month
=SUMPRODUCT(--(TEXT(Master!$D$4:$D$1754,"YYYY-MM")=TEXT($B34,"YYYY-MM")),--(Master!$J$4:$J$1754=D34),--(ISNUMBER(Master!$N$4:$N$1754)))

Seems to include data from the previous month - In tests the only way to make this data correct was to delete all data from May. However I cannot where it is going wrong.

the jate is held in B34 and the brand in D34