MrExcel Publishing
Your One Stop for Excel Tips & Solutions

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!

Posted by Aladin Akyurek on January 09, 2002 12:18 PM

Gina --

If you think your criteria will effectively discriminate records/cases you are interested in, you can use a SUMPRODUCT formula which accepts multiple criteria. How did you enter the date data?



Posted by Gina D. on January 09, 2002 12:53 PM

Thanks for getting back to me so quickly! I have a "Date of Ad" column in the "F" column. In the "H" column, I have a formula "=MONTH(F2)" that just puts what month the ad was placed in. I'll try the SUMPRODUCT formula now.

Posted by Gina D. on January 09, 2002 1:17 PM

I don't think that I am doing this right...

I can't figure out how to get the SUMPRODUCT formula to show how much money was spent in December on advertising for an "Insurance Claims Processor" position. The Insurance Claims Processor position has been advertised for a few times over the past year. I only want to find the amount of money spent on this position in December. Can the SUMPRODUCT formula do that for me?

Posted by Aladin Akyurek on January 09, 2002 3:36 PM

Re: I don't think that I am doing this right...

Gina --

Lets say that the Advertising Costs has the following:

in A job titles
in F Date of Ad
inn G amount spents ads

In the other sheet, in an appropriate cell enter:

=SUMPRODUCT(('Advertising Cost!$A2:$A100=B30)*(MONTH('Advertising Cost!$F2:$F100)=11),('Advertising Cost!$G2:$G100))

Caveats. You can't feed SUMPRODUCT whole columns like A:A as arguments. This formula as set up expects no formula-returned blanks in range G2:G100.



Posted by Gina D. on January 10, 2002 9:15 AM

It worked!!!

Oh my worked! Thank you so much for your help!