# Combining Subtotal, if, left right etc with filter

#### akenb

##### New Member
Hi,
I have a list of "Customers" in column A (A2-A270) in my first tab.
In columns B-G (listing "Item name" in B1-G1) I have listed a year slash a number: e.g. 2018/1000 (years ranging from 2017-2020.
What I want to do is to be able to form a table per customer in a secondtab depending on which customer is visible after filtering in my first tab.

Tab 1:
 Item1 Item2 Item3 Customer 1 2017/2000 2018/890 Customer 2 2020/8950 2017/780 Customer 3 2018/5000

<tbody>
</tbody>

This table in tab2 I want to have like this:
Items on rows, years in columns and in the cells in between I want to list the number given in column B-G in my first tab.

Customer 1 chosen with filter in tab1 -->
Tab2:
 2017 2018 2019 2020 Item 1 2000 Item 2 890 Item 3

<tbody>
</tbody>

I have the following formula but it doesn't take the filtering in tab 1 into consideration. How can I do this?
=IF(VALUE(LEFT(Tab1!B2;4))=Tab2!B1;VALUE(RIGHT(Tab1!B2;(LEN(Tab1!B2)-5)));0)
if
Tab1!B2= statement for item 1 customer 1
Tab2!B1= year in table in Tab2

I know you can use the formula subtotal for filtered values - but how do I integrate if, left, right formulas etc.?
It is okay that the table in Tab2 only works when there is only one customer visible in filter i Tab1.

Thank you.

### Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

Replies
4
Views
539
Replies
6
Views
127
Replies
8
Views
320
Replies
3
Views
309
Replies
5
Views
2K

1,191,517
Messages
5,987,063
Members
440,074
Latest member
Emmanuelian

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