Excel: Multiple Condition Formula??

  daddy3108 13:28 21 Mar 09
Locked

Hi, I am new and not sure how this works but I am really desperate for some help.

I have a workbook which contains 6 worksheets, each wrksht relates to an office which records staff jobtitles(L5:L100), start dates(K5:K100) etc.

On my summary tab I have a start date(B1) and end date (b2). What I am trying to acheive is an automated formula that will tell me how many people started between the two dates that equal a specific jobtitle.

I can get the formula to tell me how many say administrators there are, and I can get the formula to tell me the number of people that started between the date range, but the problem I have is joining the 2 together to make 1 formula!

Phew! Please can someone help me?

  VoG II 17:18 21 Mar 09

To get the "admin" for sheet6 try

=SUMPRODUCT(--(Sheet6!K5:K100="admin"),--(Sheet6!L5:L100>=$B$1),--(Sheet6!L5:L100<=$B$2))

  daddy3108 09:30 22 Mar 09

Thanks for sending that through. I have tried that and it returns a 0 value, which is not correct. It should be 5.

In my formula, I have added the name of the actual wrksht that I am obtaining the data from and added wildcard to Admin so that it picks up Administration and Administrator:

=SUMPRODUCT(--(MKeynes!L5:L100="*Admin*"),--(MKeynes!K5:K100>=$B$1),--(MKeynes!K5:K100<=$B$2))

This is probably something really simple, but I am damned if I can do it.

Can I send you the spreadsheet so you can see what I am trying to do? I really would be very very grateful. ;0)

  VoG II 10:15 22 Mar 09

I don't think that you can use wildcards like that. Try

=SUMPRODUCT(--(LEFT(MKeynes!L5:L100,5)="Admin"),--(MKeynes!K5:K100>=$B$1),--(MKeynes!K5:K100<=$B$2))

Also, are you sure that you have K and L the right way round (your initial description had title in K and date in L).

I'll send you a PM so you can send me the file if you are still stuck but I'll be out for most of the day.

  daddy3108 11:52 22 Mar 09

I think I need to send you the spreadsheet. You can use wildcards as I have used them before in this workbook.

Jobtitles are in L and start dates are in K, see original entry:

I have a workbook which contains 6 worksheets, each wrksht relates to an office which records staff jobtitles(L5:L100), start dates(K5:K100) etc.

If you send me a PM I will send the sprdsht and give you mbl number so that I can explain better. Sorry if I am confusing you. ;0(

  VoG II 16:12 22 Mar 09

Sorry for the confusion over the columns.

I meant that you can't use wildcards in a SUMPRODUCT formula, not that you can't use wildcards in general.

I sent you a PM earlier. If you didn't receive it click my yellow envelope to send me one.

  daddy3108 17:08 22 Mar 09

No worries, I am not explaining myself well. I have just clicked on your yellow envelope to send you a PM.

I hope you receive it. ;0/

  daddy3108 18:28 22 Mar 09

I just wanted to say a BIG BIG thank you for the help you gave me. I couldn't have done it without you.... ;0)

THANK YOU SO MUCH!!

  VoG II 18:40 22 Mar 09

You're welcome!

FWIW this was my formula

=SUMPRODUCT(--(ISNUMBER(SEARCH("Tech",MKeynes!L5:L100))),--(MKeynes!K5:K100>=$B$1),--(MKeynes!K5:K100<=$B$2))

which basically says if column L contains the text Tech and the date is between B1 and B2 then count that value.

This thread is now locked and can not be replied to.

Sniper Elite 4 review: Headshotting Nazis has never felt so good

1995-2015: How technology has changed the world in 20 years

The Best Design, Illustration, Animation and VFX Awards of 2017

WWDC 2017 dates: How to get WWDC 2017 tickets, when is WWDC 2017 and more details announced