# more complex than countif ???

#### kgkev

##### Well-known Member
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.

### Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

#### Richard Schollar

##### MrExcel MVP
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

##### Well-known Member
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

##### MrExcel MVP

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

##### Well-known Member
Thanks - Thats great

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

Code:
``=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

##### MrExcel MVP
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

##### Well-known Member
reports will only be created at the end of the month.

so only complete months need to be concidered

#### DonkeyOte

##### MrExcel MVP
in which case you can use

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

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

#### kgkev

##### Well-known Member
spot on thanks for you help.

#### kgkev

##### Well-known Member
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

Replies
1
Views
422
Replies
4
Views
93
Replies
12
Views
382
Replies
6
Views
93
Replies
0
Views
53

1,191,582
Messages
5,987,468
Members
440,097
Latest member
Wint

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back