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

[laura86]

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

 

[tweedle]

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
	A	B	C	D	E	F	G	H
1		1st	2nd	3rd	4th	5th		PER PERSON
2	John	PR	PR	PR	PR			4
3	Jessica	SI	PR	PR				3
4	Mike	MO						1
5	Molly	SI	SI	SI	SI	SI		5
6	Kristina	PR	PR	SI	SI			4
7	Kevin	MO	MO	MO	MO	MO		5
8
9
10	PER LOCATION	DAYS
11	PR	8
12	SI	8
13	MO	6
14
15	TOTAL	22
16
Sheet1