Sum Values in column if Left 3 in another column are specified, and values in a different column are in a specified named range

[dc2784]

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.

 

[Aladin Akyurek]

What is the exact range Include is referring to?

 

[dc2784]

It's in G2:G6 and has a 3 values in column C that I want to include (e.g. 804, 862, 834).

 

[Aladin Akyurek]

Whole column references are expensive. Try to change the following for the definite ranges.

=SUMPRODUCT('DATA'!A:A,--(LEFT('DATA'!B:B,3)="200"),--ISNUMBER(MATCH(LEFT('DATA'!C:C,3),Include&"",0)),--('DATA'!D:D='Date'!A1))

assuming that G2:G6 is named Include and Include contains values/items like 804, 862,etc.

 

[dc2784]

Awesome thats exactly what I wanted! Thanks!

 

[Aladin Akyurek]

Awesome thats exactly what I wanted! Thanks!

You are very welcome.