Looking for a formula that will show a result based on meeting a couple criteria
Posted by Gina D. on January 09, 2002 11:50 AM
I am trying to find a formula that will show a result if a couple of criteria are met.
I am doing a HR recruiting spreadsheet. On one sheet (Advertising Costs), I keep track of all the advertising costs. On another sheet, I keep track of the cost of background checks. One the last sheet (Total Cost of Hire), I keep track of the total amount spent to hire someone.
I have set up a formula in the "Total Cost of Hire" sheet that refers to the other 2 sheets to plug in the correct amounts (rather than typing it all in myself). The formula I have there now is "Sumif('Advertising Costs'!A:A,B30,'Advertising Costs'!G:G). The Advertising Costs column "A" contains the job titles. The "B30" cell reference refers to the job that we just hired for (for example: Admin Asst for Accounting). The Advertising Costs column "G" contains how much was spent in ads.
The trouble I'm running into is that sometimes we have to repost the same position a couple times within a year. When that happens, my formula brings up the total cost of every time that position was advertised. Since the same position may have been advertised in February and again in December, the total my formula shows is an inaccurate reflection of the amount of money that was spent to hire for a specific person.
Is there a way to use 2 criteria to get a result? Rather than have the formula just look up a certain job title and show how much was spent on that title, is it possible to also set a criteria that looks for the time of year (say, December) the position was advertised in? (I already have a column set up in my "Advertising Costs" sheet that reflects what month in which a position was advertised for.
I realize that I could just add up the expenses and type them in, but I'd be interested to see if there is a formula that could do it for me. Sorry to have babbled on so much. I just wanted to explain it as thoroughly as I could. I would appreciate any tips anyone could give. Thanks!