Sumproduct (multiple columns and one criteria) giving me an error

dejhantulip

Board Regular
Joined
Sep 9, 2015
Messages
58
Office Version
  1. 365
Platform
  1. Windows
Hello everyone!

I am trying to understand why is this not working and how to fix it. Could you please take a look at the image?

bhYCaoN.png


I am using dynamic arrays for the referencing... basically what I need is the sum of each column (F to O) that corresponds to the code that is shown in the B column (from rows 34 to 43).

Basically I am getting an #N/A error on cell D34 which should (at least in my thoughts) spill from D34 to D43.

Thank you very much for the help :)
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
I think that your only option is to do it like
Excel Formula:
=SUMPRODUCT($F$9#*($F$8#=B34))
or
Excel Formula:
=SUM(INDEX(F9#,0,MATCH(B34,F8#,0)))
and then fill down
 
Upvote 0
Actually, it looks like B34:B43 is a spilled array. If so, you can use the implicit intersect operator ( @ )...

VBA Code:
=SUM(INDEX($F$9#,0,MATCH(@$B$34#,$F$8#,0)))
 
Upvote 0
Actually, it looks like B34:B43 is a spilled array. If so, you can use the implicit intersect operator ( @ )...

VBA Code:
=SUM(INDEX($F$9#,0,MATCH(@$B$34#,$F$8#,0)))

Thank you!! It works!! However, is there a reason why the formula is not spilling? I wonder this since it is referencing the arrays with the "#" symbol... so I was expecting the answer to spill down? But I find that I have to manually copy down the formula you provided.

Do you know why is it not spilling?
 
Upvote 0
Not all formulae can spill, for instance whilst this will spill down
Excel Formula:
=INDEX(F9#,,MATCH(B34#,F8#,0))
it only returns the value from the first row in the F9# range
 
Upvote 0
Hello everyone!

I think I have made a little progress here... after watching

When I do the following:
Excel Formula:
=MMULT(F9#,SEQUENCE(ROWS(B34#),,1,0))

I get the SUM of each of the rows, but I am looking for the sum of the columns...
Can anyone take a look at it?
I would be very grateful for any help! :)
Thanks a lot!!
 
Upvote 0
Got it working!

Basically this is a scenario where MMULT is used to get the totals of columns from dynamic arrays!

Here is the code in case anyone is interested!
Excel Formula:
=TRANSPOSE(MMULT(SEQUENCE(,ROWS(B9#),1,0),F9#))

Greetings to ya'll! :)
 
Upvote 0
Solution

Forum statistics

Threads
1,213,531
Messages
6,114,172
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top