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 <>

<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>
[TD="class: xl69, width: 27"] [/TD]
[TD="class: xl72, width: 65"]A[/TD]
[TD="class: xl72, width: 65"]B[/TD]
[TD="class: xl72, width: 65"]C[/TD]
[TD="class: xl68, width: 65"][/TD]

[TD="class: xl70"]1[/TD]
[TD="class: xl73"]Bob[/TD]
[TD="class: xl73"]Done[/TD]
[TD="class: xl74, align: right"]$25.00[/TD]
[TD="class: xl68"][/TD]

[TD="class: xl70"]2[/TD]
[TD="class: xl73"]Tom[/TD]
[TD="class: xl73"]Sent[/TD]
[TD="class: xl74, align: right"]$15.00[/TD]
[TD="class: xl68"][/TD]

[TD="class: xl70"]3[/TD]
[TD="class: xl73"]Bob[/TD]
[TD="class: xl73"]Won[/TD]
[TD="class: xl74, align: right"]$15.00[/TD]
[TD="class: xl68"][/TD]

[TD="class: xl70"]4[/TD]
[TD="class: xl73"]Kim[/TD]
[TD="class: xl73"]Sent[/TD]
[TD="class: xl74, align: right"]$25.00[/TD]
[TD="class: xl68"][/TD]

[TD="class: xl71"]5[/TD]
[TD="class: xl73"]Jane[/TD]
[TD="class: xl73"]Billed[/TD]
[TD="class: xl74, align: right"]$35.00[/TD]
[TD="class: xl68"][/TD]

[TD="class: xl68, colspan: 3"]If A1:A5 = Bob[/TD]
[TD="class: xl68"][/TD]
[TD="class: xl68"][/TD]

[TD="class: xl68, colspan: 5"]and B1:B5 = Won or Billed[/TD]

[TD="class: xl68, colspan: 3"]then Sum C1:C5[/TD]
[TD="class: xl68"][/TD]
[TD="class: xl68"][/TD]

</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.