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!
 
Sort by date Sort by votes
Fluff said:
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),""))
Click to expand...
Sorry I was editing my last comment and ran out of time before it expired...

This is awesome thank you! There is just one more thing (I'm so sorry to be a pain). The information I get each month for this changes and in the most recent data I received there are multiple entries for the one location, and to get it to work I have to delete the gaps between entries otherwise it will only count the first entry. Can you help again please? Again, so sorry and really appreciate all your help!

Book2.xlsx
ABCDEFGHIJKL
1Location101LocationDays
2##########10110
3Other LeaveLeave40Normal102 
4Other LeaveLeave40Normal103 
5105 
61084.97
7Location108109 
8##########121 
9Other LeaveLeave2Normal125 
10Other LeaveLeave22Normal169 
11Other LeaveLeave15.8Normal171 
12174 
13175 
14Location181176 
15##########180 
16Other LeaveLeave1Normal1810.44
17Other LeaveLeave1Normal1823.5
18Other LeaveLeave1.5Normal183 
19184 
20185 
21Location182186 
22##########187 
23Other LeaveLeave28Normal192 
24200 
25##########300 
26Other LeaveLeave2Normal
27Other LeaveLeave22Normal
28Other LeaveLeave15.8Normal
29
30
31##########
32Other LeaveLeave1Normal
33Other LeaveLeave1Normal
34Other LeaveLeave1.5Normal
Sheet1
Cell Formulas
RangeFormula
L2:L25L2=LET(d,INDEX($G$1:$G$200,MATCH(K2,$B$1:$B$200,0)+2):G$200,IFNA(ROUND(SUM(INDEX(d,1):INDEX(d,MATCH(0,--d,0)))/8,2),""))
 
Upvote 0

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
How about
Excel Formula: 
=LET(d,INDEX($A$1:$A$200,MATCH(K2,$B$1:$B$200,0)+2):G$200,IFNA(ROUND(SUM(INDEX(d,1,7):INDEX(INDEX(d,,7),IFNA(MATCH("Location",INDEX(d,,1),0),ROWS(d))))/8,2),""))
 
Upvote 0
Fluff said:
How about
Excel Formula: 
=LET(d,INDEX($A$1:$A$200,MATCH(K2,$B$1:$B$200,0)+2):G$200,IFNA(ROUND(SUM(INDEX(d,1,7):INDEX(INDEX(d,,7),IFNA(MATCH("Location",INDEX(d,,1),0),ROWS(d))))/8,2),""))
Click to expand...
This is brilliant, thank you so much :) I really appreciate it!
 
Upvote 0
You must log in or register to reply here.

Similar threads

O
  • Solved
Put amount in DEBIT,CREDIT columns based on matching part of item
Replies
9
Views
184
Omran Y
O
B
  • Question Question
Count Text String Occurrence Across 2 Column Array But Only Count Text String Once if in Adjacent Cell
Replies
1
Views
592
Joe4
Joe4
K
  • Solved
Return Column header for MAX/MIN Value
Replies
3
Views
266
kitm
K
R
  • Question Question
VBA code to count under 5% and and non green cells
Replies
8
Views
566
footoo
F
C
  • Question Question
Need help with look up formula
Replies
5
Views
664
GuzmanServices
G

Forum statistics

Threads
1,223,884
Messages
6,175,171
Members
452,615
Latest member
bogeys2birdies

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