Excel: Multiple Condition Formula??

  daddy3108 13:28 21 Mar 2009
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 2009

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 2009

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 2009

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 2009

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 2009

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 2009

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 2009

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 2009

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.

Elsewhere on IDG sites

OnePlus 5 review

50 best online Adobe XD tutorials

iPad Pro 10.5in (2017) review

Comment connecter un MacBook à une TV ?