ExcelBanter - sumproduct

ExcelBanter (https://www.excelbanter.com/)

- Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)

- - sumproduct (https://www.excelbanter.com/excel-discussion-misc-queries/147289-sumproduct.html)

Hi,

My data is like below:

A B
1 900180111 50000
2 902180111 20000
3 911180111 15000
4 900180121 40000
5 911280121 150000

what i want to do is:

sum of column B which the code in the column A started with 90 or 91 and
ended with 80111 the answer suld be 35000

any help would be appriciated.

Farhad
--

Farhad Hodjat

My data is like below:

A B
1 900180111 50000
2 902180111 20000
3 911180111 15000
4 900180121 40000
5 911280121 150000

what i want to do is:

sum of column B which the code in the column A started with 90 or 91 and
ended with 80111 the answer suld be 35000

Based on your criteria, I think the answer should be 85000... the first
three rows of column A start with either 90 or 91 and end with 80111. This
formula produces 85000 as an answer...

=SUMPRODUCT((LEFT(A1:A100,2)={"90","91"})*(RIGHT(A 1:A100,5)="80111")*(B1:B100))

Rick