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:

[TABLE="width: 500"]
<tbody>[TR]
[TD]1
[/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]apple
[/TD]
[TD]mon
[/TD]
[TD]1
[/TD]
[TD]2
[/TD]
[TD]3
[/TD]
[TD]4
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]apple
[/TD]
[TD]tues
[/TD]
[TD]1
[/TD]
[TD]2
[/TD]
[TD]3
[/TD]
[TD]5
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]lemon
[/TD]
[TD]mon
[/TD]
[TD]5
[/TD]
[TD]2
[/TD]
[TD]3
[/TD]
[TD]6
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]orange
[/TD]
[TD]mon
[/TD]
[TD]5
[/TD]
[TD]2
[/TD]
[TD]3
[/TD]
[TD]1
[/TD]
[/TR]
</tbody>[/TABLE]

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.