SUMPRODUCT ISNUMBER Combination

[paula_444]

Hi there

I have a specific excel question and was hoping someone out there might please be able to help. I have this formula which is functioning:
=SUMPRODUCT((Income!$A$4:$A23>=B10)*(Income!$A$4:$A23<=C10)*(Income!$F$4:$F23))
However it doesn't work if there are any cells that aren't numbers. I have tried (in vain) to add an ISNUMBER clause into this but I just can't get it to function.

Please can anybody help?

Many thanks

Paula

 

[jtakw]

Hi,

Assuming you mean there may be Text within the range F4:F23, without any samples, try this:

Array formula to be confirmed with CSE (Control, Shift, Enter)

=SUM(IFERROR((Income!$A$4:$A23>=B10)*(Income!$A$4:$A23<=C10)*Income!$F$4:$F23,0))

 

[Dave Patton]

A clearer question would include an example with expected results.
I do not have your data sheet; the example that is below does not include reference to the other sheet.
The double negative -- coerces the True or False to a number.
The formula does not require array enter.
Try the following and review the formula with Excel Formula Evaluate.

SumProduct 2022a.xlsm
	A	B	C	D	E	F	G
1
2
3
4	5-Apr-22					1,000
5	15-Apr-22					ABC
6	25-Apr-22					1,020
7	5-May-22					1,030
8	15-May-22					1,040
9
10		31-Mar-22	1-May-22		Your formula		#VALUE!
11					Regular suntax		2,020
12
1e

Cell Formulas
Range	Formula
A5:A8	A5	=A4+10
G10	G10	=SUMPRODUCT(($A$4:$A23>=B10)*(A$4:$A23<=C10)*($F$4:$F23))
G11	G11	=SUMPRODUCT(--($A$4:$A23>=B10),--(A$4:$A23<=C10),($F$4:$F23))

 

[jtakw]

Basically the same solution/suggestion as @Dave Patton above, except with 2 arrays, rather than 3.
So, just switch your 2nd * with a comma, Normally entered:

=SUMPRODUCT((Income!$A$4:$A23>=B10)*(Income!$A$4:$A23<=C10),Income!$F$4:$F23)

 

[paula_444]

Thank you both so so much for your answers. I apologise for my not very helpful question (e.g. not including examples etc) but the double minuses worked perfectly! I had tried messing around with them yesterday but I had put them in the wrong place I think so it hadn't worked very well. I hugely appreciate the time you have both taken to answer me. Many thanks and have a lovely evening!

All best wishes

Paula

 

[Dave Patton]

Thanks for the feedback. Glad we could help.