Based on Multiple Criteria Pick data from Total Sales Row

[hananak]

Dear All,

I need your help to come up with a formula where it should pick the Total sales based on two criteria. 1) Month and 2) Customer Name.

The total sales row is after every two products within their respective tables. If a new product is added, then in each customer table will have all the products.

Please see the picture, to better understand.

I would appreciate your help.

 

[NickFru]

Hello - if you convert your range to a table range calling each Customer_A and Customer_B respectively and insert a column next to your data in column A with a header Customer, so something like this on each

Customer. Products. M1. M2. M3 etc
Customer A. Product A
Customer A. Product B

Lookup Info;

Customer A. Month M9 (Data Validation)

Then try this;

=SUMIFS(INDEX(Customer Range Of Numbers]], , MATCH(Pick Up Month No (eg M9, Customer Headers Of Month Range, 0)), Customer A[Customer] Range, Customer A

Hope this helps.

 

[hananak]

Hello - if you convert your range to a table range calling each Customer_A and Customer_B respectively and insert a column next to your data in column A with a header Customer, so something like this on each

Customer. Products. M1. M2. M3 etc
Customer A. Product A
Customer A. Product B

Lookup Info;

Customer A. Month M9 (Data Validation)

Then try this;

=SUMIFS(INDEX(Customer Range Of Numbers]], , MATCH(Pick Up Month No (eg M9, Customer Headers Of Month Range, 0)), Customer A[Customer] Range, Customer A

Hope this helps.

Thanks for your reply.

The problem is that I cannot make any changes to the source data nor add additional columns or convert the data to tables.

do we have alternative solution?

 

[NickFru]

OK try;

=SUM(OFFSET(A1,1, MATCH(Month No, Date Range, 0), 3, 1))

A1 is the starting point for the OFFSET which is Customer A.

 

[hananak]

Hello - if you convert your range to a table range calling each Customer_A and Customer_B respectively and insert a column next to your data in column A with a header Customer, so something like this on each

Customer. Products. M1. M2. M3 etc
Customer A. Product A
Customer A. Product B

Lookup Info;

Customer A. Month M9 (Data Validation)

Then try this;

=SUMIFS(INDEX(Customer Range Of Numbers]], , MATCH(Pick Up Month No (eg M9, Customer Headers Of Month Range, 0)), Customer A[Customer] Range, Customer A

Hope this helps.

OK try;

=SUM(OFFSET(A1,1, MATCH(Month No, Date Range, 0), 3, 1))

A1 is the starting point for the OFFSET which is Customer A.

We have to match the

OK try;

=SUM(OFFSET(A1,1, MATCH(Month No, Date Range, 0), 3, 1))

A1 is the starting point for the OFFSET which is Customer A.

I need to match cuatomer names as well.

 

[NickFru]

OK, how about;

=SUM(OFFSET(A1,MATCH(Product No, Product Range, 0), MATCH(Month No, Month Range,0), 3, 1)).

This will give per total sales say of Month 9 of 135 but if you just wanted say Month 8 total only for Product A, just change the above formula from a 3 to a 2 after the Month Range, this would give you 80 for Month 9 in this example.

 

[hananak]

OK, how about;

=SUM(OFFSET(A1,MATCH(Product No, Product Range, 0), MATCH(Month No, Month Range,0), 3, 1)).

This will give per total sales say of Month 9 of 135 but if you just wanted say Month 8 total only for Product A, just change the above formula from a 3 to a 2 after the Month Range, this would give you 80 for Month 9 in this example.

Sorry, it's not working.

 

[NickFru]

Try this on each;

=SUM(OFFSET(A1,MATCH(Customer A, A:A,0), MATCH(Month No, Month Range, 0),3,1)).

I have just tried this now and based on your answers on your question for Month 9, this will provide you with;

Customer A. Month 9. 135
Customer B. Month 9. 150