Ask Your Question
0

Formula Help Needed

asked 2015-04-23 22:12:50 +0200

this post is marked as community wiki

This post is a wiki. Anyone with karma >75 is welcome to improve it.

For LibreOffice Calc version 4.2.4.2 I'm having difficulties finding a correct formula to use for my needs. I have a time-off spreadsheet I use to total time off by an employee during each individual month. The data input would be the date, times, total time taken off, type of time taken off.

I also have a summary set of cells that say how much PTO total was taken during the month. I need a formula that can first determine the month then look at the type of time of taken and if it meets the criteria put the added values in the summary cell. Example:

..A......B......C.........D.......E.......F

MAY...13...8:00...10:00...2.0...PTO

MAY...17...1:00....2:00...1.0...WOP

JUN....10.. 8:00....4:00...8.0...PTO

So the formula would first look at column A to determine what cells belong in the month of May. If they're May then it would go on to determine if it was PTO. If it was May and PTO it would place the value from column E into a separate cell. It would have to be able to add together any values that meet that criteria.

I would greatly appreciate any help with an appropriate formula. Please excuse my attempt to make an example above of the spreadsheet. Not sure how it'll turn out with formatting.

edit retag flag offensive close merge delete

Comments

Formula don't "place" or "put" anything. They simply calculate a value for the cells they are contained in. Thus you will first have to identify the cells where the expected results should be generated and eventually displayed. To get sums regarding one or more conditions with respect to the addends or accompanying values there are available the standard functions SUMIF() and SUMIFS().

(The posting of this comment crossed the perfect answer by @m.a.riosv .)

Lupp gravatar imageLupp ( 2015-04-23 23:35:20 +0200 )edit

1 Answer

Sort by » oldest newest most voted
2

answered 2015-04-23 23:28:57 +0200

m.a.riosv gravatar image

I'm not sure if I understand well what you want, think there are three solutions:

  • SUMIFS(), can be used directly regular expressions.
  • SUMPRODUCT()
  • Menu/Data/Pivot table. No need formulas.

image description

attached sample file.

ConditionalSum.ods

edit flag offensive delete link more

Comments

Thank you so much! The SUMIFS worked perfectly! Thank you again!

wackymama gravatar imagewackymama ( 2015-04-24 00:08:01 +0200 )edit

+1 for the Pivot Table -- which LO-version did you actually use?
If have always to write Labels into the Row above Source-data.

karolus gravatar imagekarolus ( 2015-04-24 00:19:15 +0200 )edit

@karolus Version: 4.4.3.1. By the way, please be aware that in this new version, drag doesn't work to delete fields, instead select the field and use [del] key to delete it.

m.a.riosv gravatar imagem.a.riosv ( 2015-04-24 02:13:04 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2015-04-23 22:12:50 +0200

Seen: 78 times

Last updated: Apr 23 '15