# Thread: IF & AND Condition (Excel 2003)

1. ## IF & AND Condition (Excel 2003)

Hi Excel Experts,

I am using the following formula to Check the Level in c9:c16 and count the number of people under the same. Now i need to also check the BU in range a9:a16 and give me the result in c2:c5

Can someone rework on the formula and help me.

Attached is the sheet with the formula.

Regards
Baiju

2. ## Re: IF & AND Condition (Excel 2003)

Baiju,

this is a simple one. You just need to change the range and the reference to the cell holding the criteria. You need: =COUNTIF(\$A\$9:\$A\$16,\$A2).

Since you have a value (OPS) repeated in your criteria, be careful you don't simply total up the results!

Regards,

3. ## Re: IF & AND Condition (Excel 2003)

Hi Jules,

This formula will only check for the Level. The 2 tables shown in the sheet are the Data Sheet which has around 1500 records and i need a formula in Col C that check for both conditions i.e. Col A and Col Be in the Data sheet and give me a total.

I have used the formula =COUNTIF(\$C\$9:\$C\$16,\$B2) this only checks for the level and gives me a count, i also want to check the BU as well.

Regars
Baiju

4. ## Re: IF & AND Condition (Excel 2003)

I'd create a pivot table based on the data table.

5. ## Re: IF & AND Condition (Excel 2003)

HI Hans,

The pivot will give me the desired results for the count, Min, Average and max however, i wont be able to get the Median with Pivot.

Can you please me with a formula for the Median.

Regards
Baiju

6. ## Re: IF & AND Condition (Excel 2003)

I don't understand - there is nothing to calculate a median of.

7. ## Re: IF & AND Condition (Excel 2003)

Hi Hans,

My apologies, i thought if i get the formula for Count, i will build on it in my excel to get the min, median max for salary.

I have attached the excel sheet with the data and the report im building on.

Col A is my data and F2:L25 is my report. I used the conditional sum wizard and got the sum of the salary, however the sam formula does not seem to work for count, min and Median.

Can you let me know where im going wrong. also if the formula can be rebuilt to get the desired result.

Regards
Baiju

8. ## Re: IF & AND Condition (Excel 2003)

All array formulas (confirm with ctrl-shift-enter):
In P3:
=SUM(IF((\$B\$2:\$B\$640=\$G3)*(\$C\$2:\$C\$640=\$F\$3),\$D\$2: \$D\$640))
In Q3:
=Average(IF((\$B\$2:\$B\$640=\$G3)*(\$C\$2:\$C\$640=\$F\$3),\$ D\$2:\$D\$640))
In R3:
=MIN(IF((\$B\$2:\$B\$640=\$G3)*(\$C\$2:\$C\$640=\$F\$3),\$D\$2: \$D\$640))
In S3:
=MEDIAN(IF((\$B\$2:\$B\$640=\$G3)*(\$C\$2:\$C\$640=\$F\$3),\$D \$2:\$D\$640))
In T3:
=MAX(IF((\$B\$2:\$B\$640=\$G3)*(\$C\$2:\$C\$640=\$F\$3),\$D\$2: \$D\$640))

Copy P3:T3 to P15:T15

Steve

9. ## Re: IF & AND Condition (Excel 2003)

Try the attached version.

10. ## Re: IF & AND Condition (Excel 2003)

Thanks Steve & Hans,

Both the formula are working great.. this is exactly what i was looking at... you guys are great :-)

Regards
Baiju

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•