Using IF Function where logical test is for range

[Jazzmann]

Greetings, I having issues with a formula the finds a name in a range.

=IF(F10:F600="Bob",SUMPRODUCT((A10:A600="NOT SENT")*B10:B600),"")

I keep getting #value error.

Any help would be appreciated

Thanks

 

[jtakw]

Hi,

I think you mean this, use J1 formula if you don't mind 0 (zero) when there's no match for "Bob" and "Not Sent", if that's an issue, use K1 formula:

Book1
	J	K
1	0
Sheet344

Cell Formulas
Range	Formula
J1	=SUMPRODUCT((F10:F600="Bob")*(A10:A600="NOT SENT")*B10:B600)
K1	=IFERROR(1/(1/SUMPRODUCT((F10:F600="Bob")*(A10:A600="NOT SENT")*B10:B600)),"")

 

[Jazzmann]

Thanks jtakw for your response. This might make it a little more understandable. The A10:A600="NOT SENT" should have been <>

	A	B	C
1	Bob	Done	$25.00
2	Tom	Sent	$15.00
3	Bob	Won	$15.00
4	Kim	Sent	$25.00
5	Jane	Billed	$35.00
If A1:A5 = Bob
and B1:B5 = Won or Billed
then Sum C1:C5

<colgroup><col style="mso-width-source:userset;mso-width-alt:987;width:20pt" width="27"> <col style="width:49pt" width="65" span="4"> </colgroup><tbody>
</tbody>

 

[jtakw]

I having issues with a formula the finds a name in a range.

=IF(F10:F600="Bob",SUMPRODUCT((A10:A600="NOT SENT")*B10:B600),"")

Thanks jtakw for your response. This might make it a little more understandable. The A10:A600="NOT SENT" should have been <>

Well, that's Not how you show it in OP, just change the = to <> , use either E1 or F1 formula

Or, if Only Won or Billed is to be included, use either E2 or F2 formula:

Book1
	A	B	C	D	E	F
1	Bob	Done	$25.00		40	40
2	Tom	Sent	$15.00		15	15
3	Bob	Won	$15.00
4	Bob	Sent	$99.00
5	Kim	Sent	$25.00
6	Jane	Billed	$35.00
Sheet344

Cell Formulas
Range	Formula
E1	=SUMPRODUCT((A1:A6="Bob")*(B1:B6<>"Sent")*C1:C6)
E2	=SUMPRODUCT((A1:A6="Bob")*(B1:B6={"Won","Billed"})*C1:C6)
F1	=IFERROR(1/(1/SUMPRODUCT((A1:A6="Bob")*(B1:B6<>"Sent")*C1:C6)),"")
F2	=IFERROR(1/(1/SUMPRODUCT((A1:A6="Bob")*(B1:B6={"Won","Billed"})*C1:C6)),"")

EDIT: Additional options.

 

[jtakw]

Actually, you don't even need SUMPRODUCT, SUMIFS will work fine (unless your Excel is pre-2007):

Book1
	A	B	C	D	H	I
1	Bob	Done	$25.00		40	40
2	Tom	Sent	$15.00		15	15
3	Bob	Won	$15.00
4	Bob	Sent	$99.00
5	Kim	Sent	$25.00
6	Jane	Billed	$35.00
Sheet344

Cell Formulas
Range	Formula
H1	=SUMIFS(C1:C6,A1:A6,"Bob",B1:B6,"<>Sent")
H2	=SUM(SUMIFS(C1:C6,A1:A6,"Bob",B1:B6,{"Won","Billed"}))
I1	=IFERROR(1/(1/SUMIFS(C1:C6,A1:A6,"Bob",B1:B6,"<>Sent")),"")
I2	=IFERROR(1/(1/SUM(SUMIFS(C1:C6,A1:A6,"Bob",B1:B6,{"Won","Billed"}))),"")

 

[Jazzmann]

Thanks jtakw, E1 worked great. I'm going to play around with the others to see what I can come up with. Thanks again

 

[jtakw]

You're welcome.