Re: Sumproduct Function

From: Bernard Liengme (bliengme_at_stfx.TRUENORTH.ca)
Date: 10/10/04 

Date: Sun, 10 Oct 2004 20:51:27 -0300

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)*(D4:D8)),
SUMPRODUCT((B4:B8=F2)*(C4:C8=F3)*(D4:D8))

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)*(D4:D8)),
SUMPRODUCT((B4:B8=F2)*(C4:C8=F3)*(D4:D8))

hope this helps 

-- 
Bernard Liengme
www.stfx.ca/people/bliengme
remove CAPS in e-mail address
"excellover" <excellover.1dxozy@excelforum-nospam.com> wrote in message 
news:excellover.1dxozy@excelforum-nospam.com...
>
> 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
>