Hi All,
I have a large set of data (example below). I would like to Sum the values in column A, if the date is a specific date, if the LEFT 3 values in column B is "200", AND if the value in column C is in a specified named range (name: Include). Not all the data is unique which is fine. I can get it to work if I add a seperate column =LEFT(....), but I want to get it so there is no extra columns.
A B C D
23 406EFC 832 1/03/2019
23 406CCC 832 1/03/2019
85 200EFC 862 1/03/2019
42 200EDA 804 2/03/2019
67 406EFD 834 2/03/2019
I've experimented with SUMPRODUCT(--(LEFT('DATA'!B:B,3)="200")*--('DATA'!D:D='Date'!A1),'DATA'!A:A)
But I can't get the named range to fit into the formula and work.
I have a large set of data (example below). I would like to Sum the values in column A, if the date is a specific date, if the LEFT 3 values in column B is "200", AND if the value in column C is in a specified named range (name: Include). Not all the data is unique which is fine. I can get it to work if I add a seperate column =LEFT(....), but I want to get it so there is no extra columns.
A B C D
23 406EFC 832 1/03/2019
23 406CCC 832 1/03/2019
85 200EFC 862 1/03/2019
42 200EDA 804 2/03/2019
67 406EFD 834 2/03/2019
I've experimented with SUMPRODUCT(--(LEFT('DATA'!B:B,3)="200")*--('DATA'!D:D='Date'!A1),'DATA'!A:A)
But I can't get the named range to fit into the formula and work.