Help with text based If/then statement

[elburro]

I am trying to format the following formula but am not sure how to do it.

In sentence form, I'm trying to say: If column/row C59=Tom and column/row G59=**** and Column/row K59=Harry, then column/row R59 =1 or 0 if false. =IF(AND(C59="Tom"*G59="****"*K59="Harry"),1,0)

Additionally, what I'd really like to do is add a range of info.I.e.

If any rows fit this criteria they will sum the total.

=IF(AND(C29:59="Tom"*G29:59="****"*K29:59="Harry"),1,0)

WHere am I going wrong?

 

[Osvaldo Palmeiro]

Hello, elburro.
Please try this formulae and see if this is what you meant.

=IF(AND(C59="Tom",G59<>"",K59="Harry"),1,0)

=SUMPRODUCT(--(C29:C59="Tom"),--(G29:G59<>""),--(K29:K59="Harry"))

 

[elburro]

Osvaldo,

Thanks! This is not quite working yet. So - here is the closest thing I can give you for a worksheet. My computer is pretty locked down so I can't actually upload anything.

I am trying to write a formula that would sum all Social Worker III's (in column C) that speak Spanish (in column F) and are Waiting for open req names (in column J). THe result would be placed in the cell below in the following chart in Column F in the row marked Spanish III.

Hope that this makes sense. Essentially, I need formula's to total all different variations of the previous request (i.e for Social Worker II's and the differnt languages they speak etc.) but I think that I can rewrite the formula to get that if I can just get the first formula to work.

Please let me know if there is any other info I can give to help you solve this puzzle!


Thanks again!



[TABLE="width: 1324"]
<TBODY>[TR]
[TD]A
Hiring Manager</SPAN>[/TD]
[TD]B
Supervisor</SPAN>[/TD]
[TD]C
Job Title</SPAN>[/TD]
[TD]D
Count</SPAN>[/TD]
[TD]E
Lang Code</SPAN>[/TD]
[TD]F
Language </SPAN>[/TD]
[TD]G
Position No</SPAN>[/TD]
[TD]H
Vacancy Dt</SPAN>[/TD]
[TD]I
Vacated By</SPAN>[/TD]
[TD]J
Status</SPAN>[/TD]
[TD]K
Notes</SPAN>[/TD]
[TD]L
Trans Req</SPAN>[/TD]
[TD]M
Open Req</SPAN>[/TD]
[/TR]
[TR]
[TD]Jones, Bob[/TD]
[TD]Smith, Ted</SPAN>[/TD]
[TD]Social Worker III/II</SPAN>[/TD]
[TD]1</SPAN>[/TD]
[TD]B</SPAN>[/TD]
[TD]Spanish</SPAN>[/TD]
[TD]00012345</SPAN>[/TD]
[TD]4/29/2013</SPAN>[/TD]
[TD]Jones, Tom[/TD]
[TD]Waiting for open req names </SPAN>[/TD]
[TD]5/3/13 – Req # 87654 created, approved 5/20/13
5/21-5/28/2013 posted on transfer line – zero applicants 5/30/13 – Added to Open Req 2/10/13 Names rcvd from Open req </SPAN>[/TD]
[TD]87645</SPAN>[/TD]
[TD]<STRIKE>99999</STRIKE>[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]Smith, Ted</SPAN></SPAN>[/TD]
[TD]Social Worker III</SPAN>[/TD]
[TD]1</SPAN>[/TD]
[TD]A</SPAN>[/TD]
[TD]Mono</SPAN>[/TD]
[TD]00012346[/TD]
[TD]3/31/2014 FUTURE</SPAN>[/TD]
[TD]Wallace, Mary[/TD]
[TD]Waiting for trans. names</SPAN>[/TD]
[TD]03/07/14 - Need transfer req 03/21/14 - Transfer III req created Waiting for TL posting</SPAN>[/TD]
[TD]42678[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]Smith, Ted</SPAN></SPAN>[/TD]
[TD]Social Worker III</SPAN>[/TD]
[TD]1</SPAN>[/TD]
[TD]A</SPAN>[/TD]
[TD]Mono</SPAN>[/TD]
[TD]00012347[/TD]
[TD]2/17/2014</SPAN>[/TD]
[TD]Anderson, Frank[/TD]
[TD]Waiting for open req names </SPAN>[/TD]
[TD]02/11/14 – Transf Req III created
02/25/14- Transf line closes 03/04/14 03/07/14 - No candidates move to open or go to II
03/19/14 - Moving to open III req</SPAN>[/TD]
[TD]<STRIKE>78594</STRIKE>[/TD]
[TD]68745[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]Smith, Ted</SPAN></SPAN>[/TD]
[TD]Social Worker III</SPAN>[/TD]
[TD]1</SPAN>[/TD]
[TD]C</SPAN>[/TD]
[TD]Vietnamese</SPAN>[/TD]
[TD]00012348[/TD]
[TD]2/17/2014</SPAN>[/TD]
[TD]Cooper, Andy[/TD]
[TD]Waiting for open req names </SPAN>[/TD]
[TD]02/11/14 – Transf Req created
02/25/14- Transf line closes 03/04/14 03/07/14 - No candidates move to open or go to II
03/24/14 - Move to viet open req</SPAN>[/TD]
[TD]<STRIKE>17846</STRIKE>[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]Smith, Ted</SPAN></SPAN>[/TD]
[TD]Social Worker III</SPAN>[/TD]
[TD]1</SPAN>[/TD]
[TD]B</SPAN>[/TD]
[TD]Spanish</SPAN>[/TD]
[TD]00012349[/TD]
[TD]1/6/2014</SPAN>[/TD]
[TD]Delano, Richard[/TD]
[TD]Waiting for open req names </SPAN>[/TD]
[TD]12/10/13 – Trans req created
01/17/14 – On transfer line 03/04/14 - Moved to open req</SPAN>[/TD]
[TD]<STRIKE>74638</STRIKE>[/TD]
[TD]<STRIKE>06958</STRIKE> 47383[/TD]
[/TR]
[TR]
[TD]SUB TOTAL NON SW POSITIONS</SPAN>[/TD]
[TD] [/TD]
[TD]#REF!</SPAN>[/TD]
[TD]#REF!</SPAN>[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]TOTAL OPEN POSITIONS</SPAN>[/TD]
[TD] [/TD]
[TD]#REF!</SPAN>[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Waiting for Transf Req</SPAN>[/TD]
[TD]On Transfer Line</SPAN>[/TD]
[TD]Waiting for Transf Names</SPAN>[/TD]
[TD]Waiting for Names from Open Req</SPAN>[/TD]
[TD]in Interviews</SPAN>[/TD]
[TD]TOTALS</SPAN>[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD]MONO</SPAN>[/TD]
[TD]II</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD]SPANISH</SPAN>[/TD]
[TD]II</SPAN>[/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD]VIETNAMESE</SPAN>[/TD]
[TD]II</SPAN>[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD]MONO</SPAN>[/TD]
[TD]III</SPAN>[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD]SPANISH</SPAN>[/TD]
[TD]III</SPAN>[/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD]VIETNAMESE</SPAN>[/TD]
[TD]III</SPAN>[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD]MONO </SPAN>[/TD]
[TD]II/III</SPAN>[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD]SPANISH</SPAN>[/TD]
[TD]II/III</SPAN>[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD]VIETNAMESE</SPAN>[/TD]
[TD]II/III</SPAN>[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD]TOTAL</SPAN>[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[/TR]
</TBODY><COLGROUP><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL span=2></COLGROUP>[/TABLE]

 

[Excelaron]

You don't sum things or people, use COUNTIFS

 

[elburro]

Thanks. However, if I enter the formula =COUNTIF((C29:C59="Social Worker III"),(G29:G59="Spanish"),(K29:K59="Waiting for open req names")) I get an error of "You've entered too many arguments for this function."

 

[elburro]

Thanks Osvaldo for helping me to solve this issue! (=COUNTIFS($E$2:$E$6,A9,$I$2:$I$6,$C$8,$C$2:$C$6,B9))

 

[elburro]

Re: Help with text based If/then statement - SOLVED

THanks everyone who replied to this post!