Help which function to use!?!?

jamesnimmo

New Member
Joined
Dec 3, 2010
Messages
7
Hi I am trying to create an ongoing update-able spreadsheet based of an sql data table, my data looks like the below,

Customer No_

<tbody>
</tbody>
Posting Date

<tbody>
</tbody>
Sales

<tbody>
</tbody>
Customer 3

<tbody>
</tbody>
02/01/2013 00:00

<tbody>
</tbody>
394
Customer 2

<tbody>
</tbody>
15/01/2013 00:00

<tbody>
</tbody>
200
Customer 1

<tbody>
</tbody>
17/01/2013 00:00

<tbody>
</tbody>
500
Customer 1

<tbody>
</tbody>
01/02/2013 00:00

<tbody>
</tbody>
5000
Customer 3

<tbody>
</tbody>
02/02/2013 00:00

<tbody>
</tbody>
250
Customer 2

<tbody>
</tbody>
15/02/2013 00:00

<tbody>
</tbody>
650
Customer 1

<tbody>
</tbody>
17/02/2013 00:00

<tbody>
</tbody>
900
Customer 3

<tbody>
</tbody>
22/02/2013 00:00

<tbody>
</tbody>
403
Customer 3

<tbody>
</tbody>
02/03/2013 00:00

<tbody>
</tbody>
260
Customer 2

<tbody>
</tbody>
15/12/2012 00:00

<tbody>
</tbody>
970
Customer 1

<tbody>
</tbody>
17/03/2013 00:00

<tbody>
</tbody>
54
Customer 2

<tbody>
</tbody>
22/03/2013 00:00

<tbody>
</tbody>
69
Customer 3

<tbody>
</tbody>
02/04/2013 00:00

<tbody>
</tbody>
78
Customer 1

<tbody>
</tbody>
02/04/2013 00:00

<tbody>
</tbody>
365
Customer 2

<tbody>
</tbody>
05/04/2013 00:00

<tbody>
</tbody>
996

<tbody>
</tbody>

I am trying to create a sheet that then looks at this table, (it has a vast number of rows that will expand as refreshed), see example. I need the formula to look at the table and return the sum of the sales during the given month to the corresponding cell.

Customer NoDecember 2012 salesJanuary 2013 SalesFebruary 2013 SalesMarch 2013 SalesApril 2013 Sales
Customer 1
Customer 2
Customer 3

<tbody>
</tbody>

I dont think i need a pivot table but any suggestions would be helpful, i have tried a sumproduct but can't get it to work with date ranges.

Any Help would be great,thanks in anticipation.
 

Some videos you may like

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

MDCurry

Board Regular
Joined
May 3, 2005
Messages
180
I was able to get this to work:
Assume your data is in A2:C20 (Col A is Customer, B is Date, C is Sales Amnt)
At cell F2 enter a Customer name
At Cell G1 enter in the last day of a given month...say 1/31/2013
At cell G2 enter the following formula:
=SUMIFS($C$2:$C$20,$A$2:$A$20,$F2,$B$2:$B$20,">="&DATE(YEAR(G$1),MONTH(G$1),1),$B$2:$B$20,"<="&G$1)
 

par60056

Well-known Member
Joined
Jul 26, 2012
Messages
1,581
Office Version
  1. 2011
  2. 2010
Platform
  1. Windows
  2. MacOS
What happens if somebody adds a new customer to the database? That means somebody has to manually add the customer to the table and extend the formulas. The same is true as time passes and you want to add months. Pivot tables deal with new data automatically.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,657
Messages
5,597,391
Members
414,142
Latest member
Banyangt

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top