IF & AND Condition (Excel 2003)

Topic

New Reply

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

Reply | Quote

Replies

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,

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

I don’t understand – there is nothing to calculate a median of.

Reply | Quote

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:D 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

Reply | Quote

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

Reply | Quote

Try the attached version.

Reply | Quote

Thanks Steve & Hans,

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

Regards
Baiju

Reply | Quote