Array formula as SUMPRODUCT argument

[foreveryone]

Is this possible?

I have an array of IFs that's matches 1 row and returns a 4 column range, so 4 numbers. I can sum this fine but when I try passing it as the first argument in to sumproduct, I get a value! error. Here's some sample data:

1	A	B	C	D	E	F
2	apple	mon	1	2	3	4
3	apple	tues	1	2	3	5
4	lemon	mon	5	2	3	6
5	orange	mon	5	2	3	1

<tbody>
</tbody>

So I have {SUMPRODUCT(IF(A2:A5="Apple",IF(B2:B5="mon",C2:F5)),Z1:Z4})}

Where Z1:Z4 is some other 4 numbers.

In my actual data I have 4 IFs and looking at more cells but of course, shouldn't matter!

Many thanks

 

[Marcelo Branco]

Welcome to Mr Excel forum

Try
=SUMPRODUCT((A2:A5="Apple")*(B2:B5="mon")*C2:F5*Z1:Z4)
confirmed with just Enter

Hope this helps

M.

 

[foreveryone]

Welcome to Mr Excel forum

Try
=SUMPRODUCT((A2:A5="Apple")*(B2:B5="mon")*C2:F5*Z1:Z4)
confirmed with just Enter

Hope this helps

M.

Thanks very much. I'm used to using arrays but seems SUMPRODUCT might be more useful in a lot of cases.

 

[markmzz]

Hi!

You can try too this small modification in your formula:

Use Ctrl+Shift+Enter to enter the formula

=SUM(IF(A2:A5="apple",IF(B2:B5="mon",C2:F5*Z1:Z4)))

Markmzz

 

[Marcelo Branco]

Thanks very much. I'm used to using arrays but seems SUMPRODUCT might be more useful in a lot of cases.

You are welcome. Thanks for the feedback.

M.