A macro to test multiple conditions that are not in an array

laura86

New Member
Joined
Mar 8, 2011
Messages
1
I have a complicated one....

I have a calender with each day corresponding to a column and the rows are different people. The letters represent different offices the people worked in corresponding to the days of the month. For example John worked in the PR office for 4 days from the 1st to the 4th. Kristina worked in the PR office for 2 days from 1st to 2nd then in the SI office for 2 days from the 3rd to the 4th. I want to create a macro that will add up the total days worked at each place so for example the total days worked at PR was 4 from John, 2 from Jessica, 2 from Kristina so 8 days total. I also want to know how many times people worked at the PR which is 3.

1st 2nd 3rd 4th 5th
John PR 2 3 4
Jessica SI PR 2
Mike MO
Molly SI 2 3 4 5
Kristina PR 2 SI 2
Kevin MO 2 3 MO 2

The challenge in this is I can get the total days to add up if I change the numbers from counting up the days to just putting the total number of days in each column. See John no longer counts up to 4 every number is just 4. If I use sumif I can get it to add the numbers right next to the PR, however I'm then stuck with what happense in Jessica's case where no number is written next to SI because it is only 1 day, and the next day she is in a new office. I need a macro that will locate the office I request PR for example then add up the total days and also add up the total people who worked there. The problem is getting the program to add up the days based on the last number written before a new office or blank space, and if there is no number written next to a letter that counts as 1 day. I hope this was clear enough. Thank you for your help.

1st 2nd 3rd 4th 5th
John PR 4 4 4
Jessica SI PR 2
Mike MO
Molly SI 5 5 5 5
Kristina PR 2 SI 2
Kevin MO 3 3 MO 2
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

tweedle

Well-known Member
Joined
Aug 1, 2010
Messages
1,559
Welcome to MrExcel.

Do the numbers have some other significance? You seem to change them in your second example and I'm not sure why.

I need a macro that will locate the office I request PR for example then add up the total days and also add up the total people who worked there.

Could you not continue putting the office code in the cells and count them?

Does this mean you want to count the persons per location?
and also add up the total people who worked there


Here's one model with no vba, just formulas.

Excel Workbook
ABCDEFGH
11st2nd3rd4th5thPER PERSON
2JohnPRPRPRPR4
3JessicaSIPRPR3
4MikeMO1
5MollySISISISISI5
6KristinaPRPRSISI4
7KevinMOMOMOMOMO5
8
9
10PER LOCATIONDAYS
11PR8
12SI8
13MO6
14
15TOTAL22
16
Sheet1
 

Forum statistics

Threads
1,141,019
Messages
5,703,774
Members
421,315
Latest member
awaisnazir139

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
Top