Sumproduct Function

[excellover]

Frank and anyone familiar with this function , please help

I have tried the function and the link given to me by frank is ver
useful , the only catch is that the function looks to :

sumproduct(array1=condition1,array2=condition2,array3) , now here is m
problem, I will try to DRAW A SAMPLE SHEET FOR EXAMPLE

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


Here is the problem I am facing:-

1) I put the command
=SUMPRODUCT((B4:B8=F2)*(C4:C8=F3)*(D48))

This only works ok if I input two arrays , say ADAM in cell F2 an
MAZDA IN F3, if I input only ADAM IN F2 and leave F3 BLANK OR INPUT AN
LETTERS that are not in B1:B5 the formula will not pick the $400 in C
for ADAM .

MY QUESTION IS :-
How can I make SUMPRODUCT disregard any WORD I input in F3 and pick th
corresponding amount and if I input the exact criteria in F2 and F3 i
picks up the corresponding amount for the matched criteria, E.G

1) F2 = ADAM
F3 = XYZXYZ

HOW WILL SUMPRODUCT PICK C3 ( SINCE B3 IS BLANK ) and if

2) F2 = ADAM
F3 = MAZDA

HOW WILL SUMPRODUCT PICK C4 ( SINCE B4 IS MAZDA )

I know I have missed some kind of operator , I do realize the power o
this function and I am sure there will be a way around it ,

Appreciate your help.
Thank yo

 

[Bernard Liengme]

If the question is:
IF F3 is blank, then sum for the F2 value, else sum matching F2 and F3
We could use =IF(ISBLANK(F3),SUMPRODUCT((B4:B8=F2)*(D48)),
SUMPRODUCT((B4:B8=F2)*(C4:C8=F3)*(D48))

But if the problem is: When F3 has a value that occurs in column C, then do
the double match, else do the single match. Not test but is a start -
=IF(ISNA(MATCH(F3,C4:C8,FALSE)),SUMPRODUCT((B4:B8=F2)*(D48)),
SUMPRODUCT((B4:B8=F2)*(C4:C8=F3)*(D48))

hope this helps

 

[duane]

if an entry in column b is not critical, take it out of the function

=SUMPRODUCT((B4:B8=F2)*(D48))
will give all results for adam ( i think your model names are in colum
c not b)

it all depends what you really want to add u