Data is:
AMT RATE
A-I
A-II
A-III
B-I
B-II
B-III
C-I
C-II
C-III

I want to find out sumproduct of I, II and III of A B C separately, with a
single formula as:
Sales-I
Sales-II
Sales-III

Thanx!

FARAZ

Try something like this:

=SUMPRODUCT(--(RIGHT(A2:A10,LEN(A2:A10)-2)="I"),(B2:B10))



"FARAZ QURESHI" wrote:

Data is:
AMT RATE
A-I
A-II
A-III
B-I
B-II
B-III
C-I
C-II
C-III

I want to find out sumproduct of I, II and III of A B C separately, with a
single formula as:
Sales-I
Sales-II
Sales-III

Thanx!

FARAZ

Forgot to mention that it's an array formula and you need to commit with CTRL
SHIFT ENTER

"FARAZ QURESHI" wrote:

Data is:
AMT RATE
A-I
A-II
A-III
B-I
B-II
B-III
C-I
C-II
C-III

I want to find out sumproduct of I, II and III of A B C separately, with a
single formula as:
Sales-I
Sales-II
Sales-III

Thanx!

FARAZ

Assuming the source lookup range viz:
A-I
A-II
....

is in A2:A10, with corresponding amts in B2:B10

and you have the below in say, A12:A14
Sales-I
Sales-II
Sales-III

You could put this into B12 (normal ENTER):
=SUMPRODUCT(--(TRIM(MID($A$2:$A$10,SEARCH("-",$A$2:$A$10)+1,99))=TRIM(MID(A12,SEARCH("-",A12)+1,99))),B$2:B$10)
Copy down to B14 to return the required results
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"FARAZ QURESHI" wrote:
Data is:
AMT RATE
A-I
A-II
A-III
B-I
B-II
B-III
C-I
C-II
C-III

I want to find out sumproduct of I, II and III of A B C separately, with a
single formula as:


Thanx!

FARAZ

Thanx a lot Barb,

But I think you overlooked the column C showing rate for "sumproduct", or
rather I was unable to explain myself clearly. However, the following formula
worked out great:

SUMPRODUCT(--(RIGHT(A2:A10,LEN(A2:A10)-2)="I"),(B2:B10)*(C2:C10))

Thanx again!

"Barb Reinhardt" wrote:

Try something like this:

=SUMPRODUCT(--(RIGHT(A2:A10,LEN(A2:A10)-2)="I"),(B2:B10))



"FARAZ QURESHI" wrote:

Data is:
AMT RATE
A-I
A-II
A-III
B-I
B-II
B-III
C-I
C-II
C-III

I want to find out sumproduct of I, II and III of A B C separately, with a
single formula as:
Sales-I
Sales-II
Sales-III

Thanx!

FARAZ