Hi all,
I want to use an array formula instead of helper columns and have already done so for the SUM of each column, but now I need to do the same for each row. Though, this includes a SUMPRODUCT calculation and is hence a little more complex.
The cells colored in red make use of the extra columns I created, whereas the green cells are supposed to be calculated without using any interim calculations. Currently the numbers do not match by row
The sample file should make things more clear...
What is the problem with using helper columns? You can easily hide them.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
If I add helper columns for everything I do, I wouldn't have enough columns (Yes, I know, there are 16384)
Put the helper columns on a helper tab. If your data are that large, array formulae are probably not the best way to go, as they have overheads that may make your workbook sluggish.
There's a bigger story to this, hence helper columns won't work in this case! And as you can see, the 'vertical' array formula in e.g. "D3" works pretty well! Now I just need one to calculate the SUM of each row...
Perhaps this
=SUMPRODUCT(INDEX(IA!$A:$AZ,N(IF(D6:H6<>0,IF(ISNUMBER($C6),$C6))),N(IF({1},I$1:$M$1))),$D$2:$H$2)
array-entered.
Don
Please remember to mark your thread 'Solved' when appropriate.
Thanks so much Don! Just tested it intensively on my actual data set and it works just fine!! Don't know how you solve these things so quickly!!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks