Summing values based upon rules

[JMcVeigh]

Ok, so I have a time tracking spreadsheet were the users enter in 8V for 8 Vacation Hours, or 4S for 4 sick hours on the day it was taken. Only one entry per day at this point...I have used validation to only allow for valid entries for time off.

I have worked a way to get the totals for month and year and it all works, but I am thinking there might be an easier way to do this.

For each day and each type of "time off", I have a separate table on a different worksheet that only looks at the one specific type per table (Vacation, Sick, Personal etc.) which then makes my totals. I would ultimately like one formula for each month for each type of time off that totals all time that has a V - like say there are 8V, 4V and 8V all in one month (which is one row)- I would like to see 20V in the Vacation column for that employee for that month without having to direct the formula to the separate tables that I have set up...just one formula for each month for each employee.

Anyone have any ideas on what formula(s) or process I should take a look at?

 

[SkipVought]

Hi,

"I would ultimately like one formula for each month ..."

1. Do NOT use MONTHS. Use DATES. Dates can be FORMATTED to display Month, but a Month STRING, is virtually useless. faq68-5827

2. So if you have your month beginning or month ending dates across, you can summarize using one of Excel functions like SUMIF (for one criterion) or SUMPRODUCT (for multiple criteria)

Skip,
[sub]
When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large[/sub]

 

[xlbo]

Would need sumproduct for this one Skip as OP is trying to sum where cells are alpha/numeric in the same cell:

With your 8V, 4S etc in cells B2:Z2, the following should work - doesn't take dates into account however as you have not specified how and where the date / month indicators are

=SUMPRODUCT((RIGHT(B2:Z2,1)="V")*(VALUE(LEFT(B2:Z2,LEN(B2:Z2)-1))))


That will get you all your "V"s

To sum all the sick days, simply change V for S:

=SUMPRODUCT((RIGHT(B2:Z2,1)="S")*(VALUE(LEFT(B2:Z2,LEN(B2:Z2)-1))))


Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[JMcVeigh]

Thanks for the help...but I am still not getting it.

Here is a sample table, maybe it will help me ask my question better.

Name 1/1 1/2 1/3 1/4 Vac Sick
#1 8V 8V 16
#2 8V 4S 8S 8 12

I am looking to get the values under Vacation and Sick to show the values listed...any help is always appreciated!

Thanks and Happy New Year.

 

[SkipVought]

You are confusing REPORTING/DISPLAY for STORAGE.

You data ought to be STORED something like this...
[tt]
Employee PostingDate Type Hours
McVeigh, J 12/31/2007 H 8
McVeigh, J 1/1/2008 H 8
McVeigh, J 1/2/2008 V 8
McVeigh, J 1/3/2008 R 8
McVeigh, J 1/4/2008 S 8
[/tt]
Then you can easily REPORT like this...
[tt]
Emp 12/31 1/1 1/2 1/3 1/4 1/5 1/6 1/7 H V S R
McVeigh, J 8H 8H 8V 8R 8S #N/A #N/A 16 8 8 8
[/tt]
Use ConditionalFormatting to HIDE the #N/A values.

Using Named Ranges...
[tt]
Formula 1: B2: =SUMPRODUCT((Employee=$A2)*(PostingDate>=B$1)*(PostingDate<C1)*(Hours))&INDEX(Type,MATCH($A2,Employee,0)+MATCH(B$1,OFFSET(Sheet1!$A$1,MATCH($A2,Employee,0),1,COUNTIF(Employee,$A2),1),0)-1)
Formula 2: J2: =SUMPRODUCT((Employee=$A2)*(PostingDate>=B$1)*(PostingDate<I$1)*(Type=J$1)*(Hours))
[/tt]

Skip,
[sub]
When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large[/sub]

 

[xlbo]

yep - that's what I thought - formula should work fine

Have you tried entering the formula and changing the ranges to suit your data ? what were the results ?

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[SkipVought]

BTW, the Dates in the REPORT are REAL DATES, formatted to DISPLAY only the m/d.

Skip,
[sub]
When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large[/sub]

 

[JMcVeigh]

That makes sense to me - now I just have to convince the powers that be, that we should do it that way.
You guys/girls are great - thanks.

 

[SkipVought]

Are the "powers that be" IT professionals? I seriously doubt it, given your last post.

Your dictated design has SERIOUS operating and maintenance flaws.

It is impossible to use Excel's plethora of data analysis and reporting features with such a source.

It would be akin to an accountant telling a machinist how to fabricate a part, because he has little or no understanding of the machine's capabilities and limitations and the material properties.

Skip,
[sub]
When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large[/sub]

 

[yogia]

Here is a sample table, maybe it will help me ask my question better.

Name 1/1 1/2 1/3 1/4 Vac Sick
#1 8V 8V 16
#2 8V 4S 8S 8 12

I am looking to get the values under Vacation and Sick to show the values listed...any help is always appreciated!

If your entire table is in cells A1:G3, then try the following array formula ...

in cell F2: =SUM(IF(RIGHT($B2:$E2)=LEFT(F$1,1),--IF(NOT(ISBLANK($B2:$E2)),LEFT($B2:$E2,LEN($B2:$E2)-1))))

this formula is then copied to cells F2:G3

[tt]Name 1/1 1/2 1/3 1/4 Vac Sick[/tt]
[tt]#1 8V 8V 16 0[/tt]
[tt]#2 8V 4S 8S 8 12[/tt]


Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC

http://www.energyefficientbuild.com

 

[bluedragon2]

Have to say whay Skip says is a better soloution then coming up with a formula like that. Data needs to be stored in a functional manner. Reports are easy as long as your data is input in the right format. Don't fight excel, let it do the work for you...

[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[xlbo]

still think my 1st formula will work for this layout but I have to concur with Skip. the layout he has proposed would make reporting much easier and give you far greater scope for analysis

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question