Sumproduct Function

[excellover]

.................A..............B..............C
1..............MIKE........................300
2..............JERRY.......................350
3..............ADAM.......................400
4..............ADAM......MAZDA......500
5..............ADAM......TOYOTA.....450

Bernard , the formula :-

=IF(ISNA(MATCH(F3,B1:B5,FALSE)),SUMPRODUCT((A1:A5= F2)*(C1:C5)),
SUMPRODUCT((A1:A5=F2)*(B1:B5=F3)*(C1:C5))

1) The example is that those drivers regardles of what car they driv
will be paid the amount MATCHING their NAME in column C1:C5 if there i
no car make in Column B1:B5 ( e.g MIKE,JERRY and ADAM ) and regardles o
the CAR MAKE entry in F3.

2) For ADAM if he drives MAZDA OR TOYOTA he will be paid differen
amount as in cell C4 AND C5, but if HE drives any other CAR then h
will be paid 400 regardles of the CAR MAKE entry in F3.

BTW = the F3 WILL NEVER BE BLANK .

F2 = ADAM
F3 = JEEP

( SINCE ANY OTHER MAKE DOES NOT MATTER FOR ADAM EXCEPT IF FOR MAZDA AN
TOYOTA ) IT SHOULD PICK UP THE VALUE 400

THE RESULT I GOT WITH THE Formula above is = 1350 which is the tota
sum of ADAM ( I was expecting the formula to pick up the value 40
disregarding the F3 entry since THE MAKE FOR ADAM IN THE CELL B3 I
BLANK.

F2 = ADAM
F3 = MAZDA

( THE FORMULA CORRECTLY PICKED UP VALUE 500 )


How can I format the formula to disregard the entry in F3 if THERE AR
BLANK CELLS IN B1:B5 and pick the value in C1:C5 FOR the entry in F2 .

I hope I made my example clear. I am sorry for this long explanation
but I could not find any other way.

Thank you all again for all your help

 

[duane]

if there are less entries in col b than in col a disregard f3?

=IF(counta(B1:B5)<counta(a1:a5),SUMPRODUCT((A1:A5= F2)*(C1:C5)),
SUMPRODUCT((A1:A5=F2)*(B1:B5=F3)*(C1:C5))

 

[duane]

actually I think this is what you want - if f3 is not in b1:b5 only ad
up those entries where b1:b5 = blank

=IF(ISNA(MATCH(F3,B1:B5,FALSE)),SUMPRODUCT((A1:A5=F2)*(B1:B5="")*(C1:C5)),
SUMPRODUCT((A1:A5=F2)*(B1:B5=F3)*(C1:C5))