SUMPRODUCT with an IF condition?

F

FARAZ QURESHI

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
 
B

Barb Reinhardt

Try something like this:

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

Barb Reinhardt

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

Max

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

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
Click to expand...

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
 
F

FARAZ QURESHI

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!
 
Top