Looking to count number of days off in a spreadsheet based on the same condition in multiple lines

M

markbednarski

New Member
Joined
May 19, 2020
Messages
1
Office Version
  1. 2019
Platform
  1. Windows
We have a spreadsheet for 7 months of the year. Each month has the day number in each column and in each month there are three names - Mark, Ola and Atul in rows. We want to sum the total number of days taken off each month marked as "off" in each cell corresponding to the month, day and name. Hence we want to create a formula to look at each row and say it says Mark, we want to use this condition to sum up the numbers of days marked off for that row and then for all other rows. There will be 7 instances of Mark in rows, and say 3 day off in May, another two in June and them four in August. I'm think if a conditional formula that will countif the instances of "off" based on the row in the array that has the specific name. Cannot figure out how to do this. Please help.
 

Attachments

  • Excel Question.PNG
    Excel Question.PNG
    29.8 KB · Views: 9

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
Sort by date Sort by votes
Here is one way

Book1
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAG
1May12345678910111213141516171819202122232425262728293031Total
2Markoffoff2
3Olaoffoffoff3
4Atuloffoffoff3
5
6June123456789101112131415161718192021222324252627282930
7Markoff1
8Olaoffoffoffoff4
9Atuloffoffoff3
10
11July12345678910111213141516171819202122232425262728293031
12Markoffoffoff3
13Ola0
14Atuloff1
15
16Aug12345678910111213141516171819202122232425262728293031
17Markoff1
18Ola0
19Atuloffoffoffoffoffoffoffoffoffoffoffoffoff13
20
21Sept123456789101112131415161718192021222324252627282930
22Markoffoffoffoffoffoffoffoff8
23Olaoffoffoffoff4
24Atuloffoffoff3
25
26Oct
27Mark
28Ola
29Atul
30
31Nov
32Mark
33Ola
34Atul
35
36Dec
37Mark
38Ola
39Atul
40
41Total
42Mark15
43Ola11
44Atul23
Sheet2
Cell Formulas
RangeFormula
AG2:AG4, AG22:AG24, AG17:AG19, AG12:AG14, AG7:AG9AG2=COUNTIF(B2:AF2,"off")
AG42:AG44AG42=SUMIF(A1:A39,A42,AG1:AG39)
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Question
Advanced pivot conditional formatting
Replies
1
Views
379
kevin9999
K
N
  • Solved
VBA code for deleting rows based on number of days in a month
2
Replies
14
Views
1K
nitiona
N
M
  • Solved
Count Days With Entries By Month
Replies
2
Views
284
Marklarbear
M
N
  • Solved
Define range based on 3 conditions then count unique text
Replies
5
Views
176
Fluff
Fluff
F
  • Question
Excel Macro Assistance Needed for Staff Roster and Training Allocation
Replies
0
Views
302
FajnaAli
F

Forum statistics

Threads
1,214,622
Messages
6,120,576
Members
448,972
Latest member
Shantanu2024

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