Look up reference item, find all occurrences of certain text relating to that item, and count adjacent cell

T

TessieBear99

New Member
Joined
Aug 26, 2018
Messages
20
Office Version
  1. 365
Platform
  1. Windows
I don't even know if this is possible, it was hard enough figuring out the title for this post.

So to give context to my problem, I'm given a sheet from our payroll team of all the leave taken in the previous month for each of our locations within the business, and I need to add up the total days of leave allocated to each location.

I found this article Excel Formula - Find a particular occurrence which provides a formula I can use to get the different occurrences of "Days" and give me the number next to it, however I have no idea how to do that based off the location.

Below is my spreadsheet, highlighted in yellow are the locations, orange are the occurrences of "Days", and green are the numbers I need counted. I've made a list of all the locations we have (column K) and an empty column next to that (column L) to put the formula in, so that, for example, I could find 0482 in that list and next to it would show 10.

So for location 0108, I need to look up K6 in B:B, then find "Days" in column H of that block and then return the value in the adjacent cell in column G.

Employee History Report.xlsx
ABCDEFGHIJKL
1Location0482LocationDays
2##########0101
3Other LeaveLeave40.00Normal0102
4Other LeaveLeave40.00Normal0103
580.000105
610.00Days0108
7Location01080109
8##########0121
9Other LeaveLeave2.00Normal0125
10Other LeaveLeave22.00Normal0169
11Other LeaveLeave15.75Normal0171
1239.750174
134.00Days0175
14Location01810176
15##########0180
16Other LeaveLeave1.00Normal0181
17Other LeaveLeave1.00Normal0182
18Other LeaveLeave1.50Normal0183
193.500184
200.00Days0185
21Location01820186
22##########0187
23Other LeaveLeave28.00Normal0192
2428.000200
253.00Days0300
26Location01860335
27##########0336
28Other LeaveLeave4.50Normal0345
294.500346
300.00Days0348
31Location03690349
32##########0353
33Other LeaveLeave45.00Normal0351
34Other LeaveLeave45.00Normal0360
3590.000361
3610.00Days0363
37Location03720364
38##########0365
39Other LeaveLeave80.00Normal0366
4080.000367
4110.00Days0368
42Location04610369
43##########0461
44Other LeaveLeave8.00Normal0480
45Other LeaveLeave8.00Normal0481
46Other LeaveLeave8.00Normal0482
47Other LeaveLeave8.00Normal0483
48Other LeaveLeave8.00Normal0484
49Other LeaveLeave8.00Normal0600
50Other LeaveLeave8.00Normal0623
51Other LeaveLeave8.00Normal0775
52##########0776
53Other LeaveLeave3.50Normal0777
54Other LeaveLeave2.00Normal0778
55Other LeaveLeave10.00Normal0779
56Other LeaveLeave2.00Normal0888
57Other LeaveLeave10.00Normal
58Other LeaveLeave2.00Normal
59Other LeaveLeave2.00Normal
60Other LeaveLeave3.50Normal
61Other LeaveLeave2.00Normal
62Other LeaveLeave3.00Normal
63Other LeaveLeave2.00Normal
64Other LeaveLeave2.00Normal
65##########
66Other LeaveLeave8.00Normal
67##########
68Other LeaveLeave7.00Normal
69Other LeaveLeave6.50Normal
70Other LeaveLeave40.00Normal
71Other LeaveLeave8.00Normal
72Other LeaveLeave7.00Normal
73Other LeaveLeave40.00Normal
74Other LeaveLeave6.50Normal
75Other LeaveLeave7.00Normal
76Other LeaveLeave6.25Normal
77Other LeaveLeave7.00Normal
78Other LeaveLeave6.00Normal
79Other LeaveLeave6.75Normal
80Other LeaveLeave6.50Normal
81270.50
8233.00Days
Report
Cell Formulas
RangeFormula
G5G5=+G4+G3
G6,G82,G41,G30,G25,G20,G13G6=+ROUNDDOWN(G5/8,0)
G12,G19G12=SUM(G9:G11)
G24,G40,G29G24=SUM(G23)
G35G35=SUM(G33:G34)
G36G36=+ROUNDDOWN(G35/9,0)
G81G81=SUM(G44:G80)


If anyone can help at all it would be greatly appreciated, thank you!
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
Sort by date Sort by votes
How about
Fluff.xlsm
ABCDEFGHIJKL
1Location482LocationDays
2##########101 
3Other LeaveLeave40Normal102 
4Other LeaveLeave40Normal103 
580105 
610Days1084
7Location108109 
8##########121 
9Other LeaveLeave2Normal125 
10Other LeaveLeave22Normal169 
11Other LeaveLeave15.75Normal171 
1239.75174 
134Days175 
14Location181176 
15##########180 
16Other LeaveLeave1Normal1810
17Other LeaveLeave1Normal1823
18Other LeaveLeave1.5Normal183 
193.5184 
200Days185 
21Location1821860
22##########187 
23Other LeaveLeave28Normal192 
2428200 
253Days300 
Master
Cell Formulas
RangeFormula
G5G5=+G4+G3
G6,G25,G20,G13G6=+ROUNDDOWN(G5/8,0)
G12,G19G12=SUM(G9:G11)
G24G24=SUM(G23)
L2:L25L2=LET(f,FILTER($A$1:$H$100,($A$1:$A$100="Location")+($H$1:$H$100="Days")),IFNA(INDEX(f,MATCH(K2,INDEX(f,,2),0)+1,7),""))
 
Upvote 0
Solution
Fluff said:
How about
Fluff.xlsm
ABCDEFGHIJKL
1Location482LocationDays
2##########101 
3Other LeaveLeave40Normal102 
4Other LeaveLeave40Normal103 
580105 
610Days1084
7Location108109 
8##########121 
9Other LeaveLeave2Normal125 
10Other LeaveLeave22Normal169 
11Other LeaveLeave15.75Normal171 
1239.75174 
134Days175 
14Location181176 
15##########180 
16Other LeaveLeave1Normal1810
17Other LeaveLeave1Normal1823
18Other LeaveLeave1.5Normal183 
193.5184 
200Days185 
21Location1821860
22##########187 
23Other LeaveLeave28Normal192 
2428200 
253Days300 
Master
Cell Formulas
RangeFormula
G5G5=+G4+G3
G6,G25,G20,G13G6=+ROUNDDOWN(G5/8,0)
G12,G19G12=SUM(G9:G11)
G24G24=SUM(G23)
L2:L25L2=LET(f,FILTER($A$1:$H$100,($A$1:$A$100="Location")+($H$1:$H$100="Days")),IFNA(INDEX(f,MATCH(K2,INDEX(f,,2),0)+1,7),""))
Click to expand...
This is amazing, thank you so much! I had got sort of close to getting it but I hadn't managed to get it right for 0461. And your formula is much neater! Thank you! :)
 
Upvote 0
You're welcome & thanks for the feedback.
 
Upvote 0
Fluff said:
You're welcome & thanks for the feedback.
Click to expand...
I need help on this again I'm sorry! The same thing, but this time I have no orange or green cells and the hours have not been summed already.

This time I need to do =ROUND(SUM(G:G)/8,2) but only adding the numbers in column G which correspond with the specific Location number. So basically in L2 it should say 10.00; L6 should say 4.97 etc.

Does that make sense?

Book2.xlsx
ABCDEFGHIJKL
1Location101LocationDays
2##########1010
3Other LeaveLeave40Normal102 
4Other LeaveLeave40Normal103 
5105 
61080
7Location108109 
8##########121 
9Other LeaveLeave2Normal125 
10Other LeaveLeave22Normal169 
11Other LeaveLeave15.75Normal171 
12174 
13175 
14Location181176 
15##########180 
16Other LeaveLeave1Normal1810
17Other LeaveLeave1Normal182#REF!
18Other LeaveLeave1.5Normal183 
19184 
20185 
21Location182186 
22##########187 
23Other LeaveLeave28Normal192 
24200 
25300 
Sheet1
Cell Formulas
RangeFormula
L2:L25L2=LET(f,FILTER($A$1:$H$100,($A$1:$A$100="Location")+($H$1:$H$100="Days")),IFNA(INDEX(f,MATCH(K2,INDEX(f,,2),0)+1,7),""))
 
Upvote 0
How about
Excel Formula: 
=LET(d,DROP($G$1:$G$25,MATCH(K2,$B$1:$B$25,0)+1),IFNA(ROUND(SUM(TAKE(d,MATCH(0,--d,0)))/8,2),""))
 
Upvote 0
Fluff said:
How about
Excel Formula: 
=LET(d,DROP($G$1:$G$25,MATCH(K2,$B$1:$B$25,0)+1),IFNA(ROUND(SUM(TAKE(d,MATCH(0,--d,0)))/8,2),""))
Click to expand...
It's giving me a #NAME? error

Book2.xlsx
ABCDEFGHIJKL
1Location482LocationDays
2##########101#NAME?
3Other LeaveLeave40Normal102 
4Other LeaveLeave40Normal103 
5105 
61080
7Location108109 
8##########121 
9Other LeaveLeave2Normal125 
10Other LeaveLeave22Normal169 
11Other LeaveLeave15.8Normal171 
12174 
13175 
14Location181176 
15##########180 
16Other LeaveLeave1Normal1810
17Other LeaveLeave1Normal182#REF!
18Other LeaveLeave1.5Normal183 
19184 
20185 
21Location182186 
22##########187 
23Other LeaveLeave28Normal192 
24200 
25300 
Sheet1
Cell Formulas
RangeFormula
L2L2=LET(d,DROP($G$1:$G$25,MATCH(K2,$B$1:$B$25,0)+1),IFNA(ROUND(SUM(TAKE(d,MATCH(0,--d,0)))/8,2),""))
L3:L25L3=LET(f,FILTER($A$1:$H$100,($A$1:$A$100="Location")+($H$1:$H$100="Days")),IFNA(INDEX(f,MATCH(K3,INDEX(f,,2),0)+1,7),""))
 
Upvote 0
TessieBear99 said:
It's giving me a #NAME? error

Book2.xlsx
ABCDEFGHIJKL
1Location482LocationDays
2##########101#NAME?
3Other LeaveLeave40Normal102 
4Other LeaveLeave40Normal103 
5105 
61080
7Location108109 
8##########121 
9Other LeaveLeave2Normal125 
10Other LeaveLeave22Normal169 
11Other LeaveLeave15.8Normal171 
12174 
13175 
14Location181176 
15##########180 
16Other LeaveLeave1Normal1810
17Other LeaveLeave1Normal182#REF!
18Other LeaveLeave1.5Normal183 
19184 
20185 
21Location182186 
22##########187 
23Other LeaveLeave28Normal192 
24200 
25300 
Sheet1
Cell Formulas
RangeFormula
L2L2=LET(d,DROP($G$1:$G$25,MATCH(K2,$B$1:$B$25,0)+1),IFNA(ROUND(SUM(TAKE(d,MATCH(0,--d,0)))/8,2),""))
L3:L25L3=LET(f,FILTER($A$1:$H$100,($A$1:$A$100="Location")+($H$1:$H$100="Days")),IFNA(INDEX(f,MATCH(K3,INDEX(f,,2),0)+1,7),""))
Click to expand...
I think I found the issue - I don't have the TAKE or DROP functions
 
Upvote 0
How about
Excel Formula: 
=LET(d,INDEX($G$1:$G$25,MATCH(K2,$B$1:$B$25,0)+2):G$25,IFNA(ROUND(SUM(INDEX(d,1):INDEX(d,MATCH(0,--d,0)))/8,2),""))
 
Upvote 0
That's amazing!! Thank you so much! :) You're a lifesaver. And I've asked my IT dept to update Excel so I should get those new formula functions soon. Appreciate your help very much!
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Question
Count Text String Occurrence Across 2 Column Array But Only Count Text String Once if in Adjacent Cell
Replies
1
Views
463
Joe4
Joe4
R
  • Question
VBA code to count under 5% and and non green cells
Replies
8
Views
403
footoo
F
C
  • Question
Need help with look up formula
Replies
5
Views
475
GuzmanServices
G
W
  • Solved
Removing Data Validation List Items Once Selected In Another Cell
Replies
3
Views
421
James006
James006
W
  • Solved
Count the Number of Unique Sequences
Replies
6
Views
555
William53
W

Forum statistics

Threads
1,215,868
Messages
6,127,413
Members
449,382
Latest member
DonnaRisso

Share this page

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