Sum total based on data from another worksheet

CA_Punit

Well-known Member
Joined
Nov 18, 2019
Messages
576
Office Version
365
Platform
Windows
Try This Does it Work

Book1
ABCD
1Lookup
2NAME1EIGHT14
3NAME2SIXSIX12
4NAME1SIX14
5NAME3EIGHTEIGHT16
6
7
Sheet1
Cell Formulas
RangeFormula
D2:D5D2=SUM(IFERROR(LOOKUP(($A$2:$A$5=A2)*IFERROR(MATCH($B$2:$C$5,{"Eight","Six"},0),0),{1;2},{8;6}),0))
 

Some videos you may like

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

Mux99

Board Regular
Joined
Apr 15, 2019
Messages
56
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.
Text will be the same as op and is needed. My only other option is to add a helper row with numbers under every row with data but this will double the size of my worksheet and slow down other formulas and CF.
 

Mux99

Board Regular
Joined
Apr 15, 2019
Messages
56
Is post 31 working????
Book1.xlsx
ABCD
1TOTAL
2NAME1EIGHT14
3NAME2SIXSIX12
4NAME1SIX14
5NAME3EIGHTEIGHT16
6
7EIGHT8
8SIX6
Sheet2
Cell Formulas
RangeFormula
D2:D5D2=SUM(IFERROR(LOOKUP(($A$2:$A$5=A2)*IFERROR(MATCH($B$2:$C$5,$A$7:$A$8,0),0),{1;2},$B$7:$B$8),0))
Press CTRL+SHIFT+ENTER to enter array formulas.


Yes, it's working. Can you confirm that I'm linking the lookup table correctly?

Thanks again to both for all your help.
 

CA_Punit

Well-known Member
Joined
Nov 18, 2019
Messages
576
Office Version
365
Platform
Windows
This formula will not result in correct answer as look up function is based on Approximate match. See this example

Book1
DEFGH
14Two9Two2
15Four4
16Three3
17Five5
18One1
19Six6
20Eight8
21Seven7
22Nine9
Sheet1
Cell Formulas
RangeFormula
E14E14=LOOKUP(D14,G14:G22,H14:H22)


It is returning 9 for TWO
 

CA_Punit

Well-known Member
Joined
Nov 18, 2019
Messages
576
Office Version
365
Platform
Windows
However i missed the {1;2} this make the formula error free.
Anyway please ensure that $A$7:$A$8 and {1;2} are related.

See this

Book1
ABCD
1TOTAL
2NAME1EIGHT21
3NAME2SIXone7
4NAME1SIX21
5NAME3EIGHTEIGHT16
6Name1FourThree21
7Name 5TwoNine11
8Name6ThreeSeven10
9
10
11
12
13EIGHT8
14SIX6
15One1
16Five5
17Two2
18Three3
19Nine9
20Four4
21Seven7
22
Sheet3
Cell Formulas
RangeFormula
D2:D8D2=SUM(IFERROR(LOOKUP(($A$2:$A$8=A2)*IFERROR(MATCH($B$2:$C$8,$A$13:$A$21,0),0),{1;2;3;4;5;6;7;8;9},$B$13:$B$21),0))
 

Mux99

Board Regular
Joined
Apr 15, 2019
Messages
56
Book1
ABCDE
1HELPERTOTAL
2NAME1EIGHT814
3NAME2SIXSIX1212
4NAME1SIX614
5NAME3EIGHTEIGHT1616
6
7EIGHT8
8SIX6
Sheet1
Cell Formulas
RangeFormula
D2:D5D2=SUM(IFERROR(INDEX($B$7:$B$8,N(IF(1,MATCH(B2:C2,$A$7:$A$8,0)))),0))
E2:E5E2=SUMIF($A$2:$A$5,A2,$D$2:$D$5)
Press CTRL+SHIFT+ENTER to enter array formulas.


I ended up using the formula above with a helper column as it seems faster than Vlookup/Lookup formulas. Is there an easy way to join these two formulas together to eliminate the helper column?

Thanks to both for your help.
 

Watch MrExcel Video

Forum statistics

Threads
1,102,351
Messages
5,486,363
Members
407,542
Latest member
Tyronaught

This Week's Hot Topics

Top