COMBINED FORMULA FOR MONTHLY SALES

[amvega]

Hi,


I am working in a spreadsheet in which I want to sum totals from a second tab that have daily sales to an specific company and specific store but that belongs to an specific month. I enter sales daily in the second tab and i want the first tab (the master) to automatically sumarize this totals if the store name, company name and the days that i manually imputed in the second tab applies to the first tab; if not return the result in 0.


For example: from second tab (I am putting $100 sales from July 1 to July 15 - in Cells A 3 and A 4) the name of the store is Lowes and the Company name is General Electric.

In the first tab (the master) I have the name Lowes, the month of July and the name of the Company GE and I need the result to come back in A1 $100; and if the sales were not made in the month of July to come back to 0 total. The formula will look in all the second tab if there were any sales within that month within that store, whitin that company and if so, it will continue to summarize that automatically in the first tab (the master).


I hope i make sense; please help!


Thank you in advance,

Angie

 

[Luke M]

I'm not exactly sure where the dates are in your 2nd tab...I'm thinking they're in row 2? The only other trick is the request to look by month, but no biggie.

=SUMPRODUCT(--(TEXT('Sheet2'!A2:Z2,"mmm")=TEXT(DateCell,"mmm")),--('Sheet2'!A4:Z4=StoreName),--('Sheet2'!A5:Z5=CompanyName),'Sheet2'!A3:Z3)


Again, I wasn't quite sure where everything is. Hopefully you can adapt to your use?


Alternatively, I'd suggest building a PivotTable. That would give you the most versatility with picking different criteria and seeing the total sales.

 

[amvega]

The person that I am doing this for is not "friendly" with pivot tables and want to have the data accruing that she can see every day of the month as i am putting the data sales daily. the problem that I am having is combining the sum of all the sales daily in tab b to be collectively sumarized in tab a. in addition in tab b i have colum b with dates from heading data and in colum c dates to data heading.


let me try then....


Thanks a bunch!

 

[amvega]

Is not working is comming back with a value error....


Let me try again.....


This is from tab B: where the manual data will be imputed:


A1 - Company B1 STORE C1 D 1 F1

EFI EFI SALES FROM SALES TO ME (UTILITY CO.)

07/01/11 07/15/11 $100.00


Tab A should feed from tab b and look like this:


A1 Company B1 Store C1

EFI EFI July


C2

ME

 

[Luke M]

When using SUMPRODUCT, make sure all the ranges are the same size. It's still not clear on tab B which column contains what data. could you list it out, like:

Col A: company

col B: Store

etc.

 

[amvega]

TAB B DATA IS AS FOLLOWS:


COLUMN A = COMPANY (THIS IS THE HEADING)

COLUMN A1 = EFI (THAT IS THE ACTUAL NAME OF THE COMPANY)

COLUMN B1 = STORE( THIS IS THE HEADING)

COLUMN B2 = EFI (THIS IS THE ACTUAL STORE NAME)

COLUMN C1 = FROM (THIS IS THE HEADING FOR THE DATE SALES RANGE STARTING DATE)

COLUMN D1 = TO (THIS TIS THE HEADING FOR THE DATE SALES RANGE END DATE)

COLUMN E = I HAVE IT EMPTY WITH A GRAY LINE DOWN TO SEPARATE THE DATA

COLUMN F1 = UTILITY COMPANY NAME( THIS IS THE HEADING NAME)

COLUMN F2 = ME (THIS IS THE ACTUAL UTILITY COMPANY NAME


Now = every day i receive sales for the company EFI and their store name is EFI as well. I have to put the data from when to when is the sales eg. 07/01/11 to 07/15/11; that will be in columns c and d respectively. then in column f (under me) i have to put the amount of sales e.g. $100 and i will continue like that every day, every month until next year at this time.


I want to in tab A that whatever i put in tab b for that belongs to the month of july automatically sumarize in tab A.


The tab A reads as follows:


column a - cell 2 = Company (this is the actual heading name)

COLUMN a - CELL 5 = EFI ( this is the actual name of the company)

COLUMN b - CELL 2 = Store (this is the actual heading for the store)

COLUMN b - CELL 5 = EFI (this is the actual name of the store)

column c - cell 1 = July (this is the month that I want to report and is my heading)

column c - cell 2 = ME (this is the utility company i want to report and is my heading)


Hope this helps...

 

[Luke M]

The sales data needs to go in its own column, say col G. Mixing it with col F which is already used for utility name will mess things up.

With that, and using the references stated, formula on Tab A is:

=SUMPRODUCT(--('Tab B'!A2:A100=A5),--('Tab B'!B2:B100=B5),--(TEXT('Tab B'!C2:C100,"mmmm")=C1),--('Tab B'!F2:F100=C2),'Tab B'!G2:G100)