Sumproduct Function

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: excellover (excellover.1dxozy_at_excelforum-nospam.com)
Date: 10/10/04 

Date: Sun, 10 Oct 2004 18:20:24 -0500

Frank and anyone familiar with this function , please help

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

sumproduct(array1=condition1,array2=condition2,array3) , now here is my
problem, I will try to DRAW A SAMPLE *** 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)*(D4:D8))

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

MY QUESTION IS :-
How can I make SUMPRODUCT disregard any WORD I input in F3 and pick the
corresponding amount and if I input the exact criteria in F2 and F3 it
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 of
this function and I am sure there will be a way around it ,

Appreciate your help.
Thank you 

-- 
excellover
------------------------------------------------------------------------
excellover's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=7059
View this thread: http://www.excelforum.com/showthread.php?threadid=268038
Quantcast