Question using sumif

[ajc623]

I am trying to have a summary page which will give me sales numbers for each year for 30 specific customers. I have a data sheet with all sales going back to 2010 that is arrange this way

Column A - item
Column B - Date
Column E - Customer Name
Column I - Amount

the other columns are date I need but are not relevant to this.
On the summary page I want to have

Column A - Customer name
Column B - 2010
Column C - 2011 and so on

My goal is to be able to enter the year in row 1 and have the data automatically update for each customer listed in Column A. One other minor issue is most of the names will match up exactly but there are several customers who order from different locations and I would like to group those together under one "parent company" name. Any help would be greatly appreciated.

Andrew

 

[Marcelo Branco]

Assuming Data as first sheet name, maybe something like this

Formula in B2 of Summary sheet
=SUMIFS(Data!$I:$I,Data!$E:$E,$A2,Data!$B:$B,">="&DATE(B$1,1,1),Data!$B:$B,"<="&DATE(B$1,12,31))
copy across and down

Hope this helps

M.

 

[oldbrewer]

01/01/2015

01/01/2016

01/01/2017

fred

1625

1175

0

item

date

custname

amount

bill

1250

1725

0

item1

03/01/2015

fred

175

sid

1350

1800

0

item2

02/02/2015

bill

200

8925

item3

04/03/2015

sid

225

item4

03/04/2015

fred

250

item5

03/05/2015

bill

275

item6

02/06/2015

sid

300

item2

02/07/2015

fred

325

item3

01/08/2015

bill

350

I do not understand your minor issue….

item4

31/08/2015

sid

375

item5

30/09/2015

fred

400

unless you mean in this example you want to treat sid and bill as one person…

item6

30/10/2015

bill

425

item1

29/11/2015

sid

450

item2

29/12/2015

fred

475

item3

28/01/2016

bill

500

item4

27/02/2016

sid

525

item5

28/03/2016

fred

550

item6

27/04/2016

bill

575

item2

27/05/2016

sid

600

item3

26/06/2016

fred

625

item4

26/07/2016

bill

650

item5

25/08/2016

sid

675

8925

<colgroup><col><col><col span="7"><col span="3"><col span="4"></colgroup><tbody>
</tbody>

 

[ajc623]

Assuming Data as first sheet name, maybe something like this

Formula in B2 of Summary sheet
=SUMIFS(Data!$I:$I,Data!$E:$E,$A2,Data!$B:$B,">="&DATE(B$1,1,1),Data!$B:$B,"<="&DATE(B$1,12,31))
copy across and down

Hope this helps

M.

Thanks for the quick reply. That works great when the customer name in the summary sheet matches exactly with what is in the data sheet but is there a way to get it to work when they dont match exactly. For example say I have Acme California and Acme Wisconsin as customers on the data sheet and I want to have just Acme on the summary sheet. I have several of these customers with 2-6 locations for each

Andrew

 

[ajc623]

Not sure what you mean by minor issue, but this is not something that I have been able to figure out on my own and thought I would seek others help. If you read my original post as well as my follow up one of my main issues is wanting to combine a customer who orders from multiple locations and that is how it is entered on the raw data sheet but I would like to combine those to see how that customer as a whole is ordering. For example say I have Acme California and Acme Wisconsin as customers on the data sheet and I want to have just Acme on the summary sheet. I have several of these customers with 2-6 locations for each

 

[Marcelo Branco]

For this particular case, names beginning with ACME, you can use a wildcard. Something like this

=SUMIFS(Data!$I:$I,Data!$E:$E,$A2&"*",Data!$B:$B,">="&DATE(B$1,1,1),Data!$B:$B,"<="&DATE(B$1,12,31))

where A2 = ACME

M.

 

[ajc623]

Thanks! that works perfectly

Andrew

 

[Marcelo Branco]

You are welcome. Glad to help.

M.