# 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 Rate1 Laborer Group 5 2 CLASS CAT ST Rate OT Rate Sun/Hol Rate3 Laborer Group 1 \$9.00 \$13.50 \$18.004 Laborer Group 2 \$9.50 \$14.25 \$19.005 Laborer Group 3 \$10.00 \$15.00 \$20.006 Laborer Group 4 \$10.50 \$15.75 \$21.007 Laborer Group 5 \$11.00 \$16.50 \$22.008 Laborer Group 6 \$11.50 \$17.25 \$23.009 Laborer Group 7 \$12.00 \$18.00 \$24.0010 Laborer Group 8 \$12.50 \$18.75 \$25.00FOR 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,””)ORFOR 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

#1
June 8, 2011 at 17:45:44
 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 & E1See how that works.MIKEhttp://www.skeptic.com/