Solved Excel if and statement

June 8, 2011 at 17:08:17
Specs: Windows 7, Intel Core2Duo/8GB
If A1 and B1 match a row within A3:B10, then show values of matched row in columns C, D & E.
A B C D E
CLASS CAT ST Rate OT Rate Sun/Hol Rate
1 Laborer Group 5
2 CLASS CAT ST Rate OT Rate Sun/Hol Rate
3 Laborer Group 1 $9.00 $13.50 $18.00
4 Laborer Group 2 $9.50 $14.25 $19.00
5 Laborer Group 3 $10.00 $15.00 $20.00
6 Laborer Group 4 $10.50 $15.75 $21.00
7 Laborer Group 5 $11.00 $16.50 $22.00
8 Laborer Group 6 $11.50 $17.25 $23.00
9 Laborer Group 7 $12.00 $18.00 $24.00
10 Laborer Group 8 $12.50 $18.75 $25.00

FOR C1, answer should be “$11.00”: =IF(AND(A1=A3:A10, B1=B3:B10),C3:C10,””)
FOR D1, answer should be “$16.50”: =IF(AND(A1=A3:A10, B1=B3:B10),D3:D10,””)
FOR E1, answer should be “$22.00”: =IF(AND(A1=A3:A10, B1=B3:B10),E3:E10,””)

OR

FOR C1, answer should be “$11.00”: =IF(AND(COUNTIF(A3:A10,A1),COUNTIF(B3:B10,B1)),C3:C10,"")
FOR D1, answer should be “$16.50”: =IF(AND(COUNTIF(A3:A10,A1),COUNTIF(B3:B10,B1)),D3:D10,"")
FOR E1, answer should be “$22.00”: =IF(AND(COUNTIF(A3:A10,A1),COUNTIF(B3:B10,B1)),E3:E10,"")

What’s wrong with my statements. Please advise. Thanks.


See More: Excel if and statement

Report •


#1
June 8, 2011 at 17:45:44
✔ Best Answer
Try this:

With your data looking like this:

       A          B       C       D       E
 1) Laborer	Group 5	$11.00 	$16.50	$22.00
 2) 				
 3) Laborer	Group 1	$9.00 	$13.50	$18.00
 4) Laborer	Group 2	$9.50 	$14.25	$19.00
 5) Laborer	Group 3	$10.00 	$15.00	$20.00
 6) Laborer	Group 4	$10.50 	$15.75	$21.00
 7) Laborer	Group 5	$11.00 	$16.50	$22.00
 8) Laborer	Group 6	$11.50 	$17.25	$23.00
 9) Laborer	Group 7	$12.00 	$18.00	$24.00
10) Laborer	Group 8	$12.50 	$18.75	$25.00

In cell C1 enter the formula:

=SUMPRODUCT(--($A$3:$A$10=$A$1),--($B$3:$B$10=$B$1),(C3:C10))

Then drag it right for cells D1 & E1

See how that works.

MIKE

http://www.skeptic.com/


Report •

#2
June 8, 2011 at 22:52:01
Yes, it worked like a charm. Thank you so much, Mike!

Report •
Related Solutions


Ask Question