Sum total based on data from another worksheet

CA_Punit

Well-known Member
Joined
Nov 18, 2019
Messages
576
Office Version
365
Platform
Windows
How did your query data set changed from the OP.

Anyway without helper column it can be calculated however you have to hard code the Criteria with value in the formula itself.
 

Some videos you may like

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

Mux99

Board Regular
Joined
Apr 15, 2019
Messages
56
How did your query data set changed from the OP.

Anyway without helper column it can be calculated however you have to hard code the Criteria with value in the formula itself.
That was just an easier example as I would need the formula to work on 2000 rows.

Any help would be appreciated.
 

CA_Punit

Well-known Member
Joined
Nov 18, 2019
Messages
576
Office Version
365
Platform
Windows
Book1
ABCDE
1TOTAL
2Name1EIGHT14??? How did you get 14 here
3Name2SIXSIX12ok
4Name1SIX14??? How did you get 14 here
5Name3EIGHTEIGHT16
6
Sheet1


Please reply (What is your expected Result)
 

Mux99

Board Regular
Joined
Apr 15, 2019
Messages
56
Book1
ABCDE
1TOTAL
2Name1EIGHT14??? How did you get 14 here
3Name2SIXSIX12ok
4Name1SIX14??? How did you get 14 here
5Name3EIGHTEIGHT16
6
Sheet1


Please reply (What is your expected Result)
Since names in column A row 2 & 4 match, then SUM B2:C2 + B4:C4. Is this possible without a helper column?
 

CA_Punit

Well-known Member
Joined
Nov 18, 2019
Messages
576
Office Version
365
Platform
Windows
So i have entered only 2 Lookup values "Eight and Six, If you have more values than you can design a lookup table for automatic updation

Book1
ABCD
1TOTAL
2Name1EIGHT14
3Name2SIXSIX12
4Name1SIX14
5Name3EIGHTEIGHT16
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))
 

Mux99

Board Regular
Joined
Apr 15, 2019
Messages
56
Cell Formulas
RangeFormula
D2:D5D2=SUMPRODUCT(($A$2:$A$5=A2)*IFERROR(VLOOKUP($B$2:$C$5,{"Eight",8;"Six",6},2,0),0))
Not sure why but my Excel 2016 is driving me crazy. When I tried the following formula, it resulted in D2 & D4 = 16, D3 & D5 = 8, which doesn't make any sense at all.

I then tried the exact same formula in google spreadsheets and it seems to work. Will try again tomorrow on Office 365 but i'm guessing it should work too. Thanks alot
 

CA_Punit

Well-known Member
Joined
Nov 18, 2019
Messages
576
Office Version
365
Platform
Windows
Can you post the result using xl2bb

xl2bb is an excel addin available in the right hand side of reply box. Download and unzipp it and run. Then you will get an Mr Excel Addin.
Lets try why office 2016 is resulting in error.
Have you tried to confirm the formula with Ctrl+shift+Enter
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
42,669
Office Version
365
Platform
Windows
With 2016 try confirming the formula in D2 with Ctrl Shift Enter, rather than just Enter & then drag down.
 

Mux99

Board Regular
Joined
Apr 15, 2019
Messages
56
Can you post the result using xl2bb

xl2bb is an excel addin available in the right hand side of reply box. Download and unzipp it and run. Then you will get an Mr Excel Addin.
Lets try why office 2016 is resulting in error.
Have you tried to confirm the formula with Ctrl+shift+Enter
Book1
ABCD
1TOTAL
2NAME1EIGHT16
3NAME2SIXSIX8
4NAME1SIX16
5NAME3EIGHTEIGHT8
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))
Press CTRL+SHIFT+ENTER to enter array formulas.

I entered the same formula in Office 2016 (with Ctrl+Shift+Enter) and google spreadsheets and got a different result for some reason so I'm not sure exactly what happened.
 

Watch MrExcel Video

Forum statistics

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

This Week's Hot Topics

Top