Sum total based on data from another worksheet

CA_Punit

Well-known Member
Joined
Nov 18, 2019
Messages
576
Office Version
365
Platform
Windows
Select the D2 to D5 and Press F2 and Then Press Control+Shift+Enter
 

Some videos you may like

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

Mux99

Board Regular
Joined
Apr 15, 2019
Messages
56
Select the D2 to D5 and Press F2 and Then Press Control+Shift+Enter
I tried this and D2 to D5 all equal 14. This is correct for D2, but then D3 to D5 are also referencing ($A$2:$A$5=A2) as they all have the same formula. If I try to edit the formula or insert a row or column, the following error displays: You can't change part of an array.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
42,669
Office Version
365
Platform
Windows
If you select D3 & use the evaluate button on the formula tab do you see this as the last step
1595788704862.png
 

Mux99

Board Regular
Joined
Apr 15, 2019
Messages
56
If you select D3 & use the evaluate button on the formula tab do you see this as the last step
View attachment 19032
All steps are listed below:

SUMPRODUCT(($A$2:$A$5=A3)*IFERROR(VLOOKUP($B$2:$C$5,{"Eight",8;"Six",6},2,0),0))
SUMPRODUCT(({"NAME1";"NAME2";"NAME1";"NAME3"}="NAME2")*IFERROR(VLOOKUP($B$2:$C$5,{"Eight",8;"Six",6},2,0),0))
SUMPRODUCT(({FALSE;TRUE;FALSE;FALSE})*IFERROR(VLOOKUP($B$2:$C$5,{"Eight",8;"Six",6},2,0),0))
SUMPRODUCT(({FALSE;TRUE;FALSE;FALSE})*IFERROR(VLOOKUP("EIGHT",{"Eight",8;"Six",6},2,0),0))
SUMPRODUCT(({FALSE;TRUE;FALSE;FALSE})*IFERROR(8,0))
SUMPRODUCT(({FALSE;TRUE;FALSE;FALSE})*8)
SUMPRODUCT(({0;8;0;0})
8
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
42,669
Office Version
365
Platform
Windows
In that case it appears that the formula only works with dynamic aware Excel. Which is 365 with the dynamic array functions.
 

CA_Punit

Well-known Member
Joined
Nov 18, 2019
Messages
576
Office Version
365
Platform
Windows
Try only this in VLOOKUP($B$2:$C$5,{"Eight",8;"Six",6},2,0) in E2 and Drag down till F5 and post the result using xl2bb
 

Mux99

Board Regular
Joined
Apr 15, 2019
Messages
56
Try only this in VLOOKUP($B$2:$C$5,{"Eight",8;"Six",6},2,0) in E2 and Drag down till F5 and post the result using xl2bb
Book1.xlsx
ABCDE
1TOTAL
2NAME1EIGHT168
3NAME2SIXSIX88
4NAME1SIX168
5NAME3EIGHTEIGHT88
Sheet1
Cell Formulas
RangeFormula
D2:D5D2=SUMPRODUCT(($A$2:$A$5=A2)*IFERROR(VLOOKUP($B$2:$C$5,{"Eight",8;"Six",6},2,0),0))
E2:E5E2=VLOOKUP($B$2:$C$5,{"Eight",8;"Six",6},2,0)
Press CTRL+SHIFT+ENTER to enter array formulas.

Is there a workaround for this without dynamic array formulas as I would prefer if this would be compatible with older excel versions too.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
42,669
Office Version
365
Platform
Windows
Why not just put the numbers into the cells, rather than the words?
 

Mux99

Board Regular
Joined
Apr 15, 2019
Messages
56
Why not just put the numbers into the cells, rather than the words?
Conditional formatting is being used depending on the text so I can't just use numbers. All matching text need to be linked to a number, and I need the total sum based on the name in Column A as they are listed multiple times.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
42,669
Office Version
365
Platform
Windows
Why not just change the CF rules to look at numbers instead?
At the end of the day numbers are numbers & should be be stored as such, it makes everything else so much easier.
 

Watch MrExcel Video

Forum statistics

Threads
1,102,351
Messages
5,486,358
Members
407,541
Latest member
Emilybuhman

This Week's Hot Topics

Top