Results 1 to 10 of 10
Thread: IF & AND Condition (Excel 2003)

20070927, 11:53 #1
 Join Date
 Jul 2006
 Location
 Bangalore, India
 Posts
 180
 Thanks
 1
 Thanked 0 Times in 0 Posts
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

20070927, 15:26 #2
 Join Date
 Sep 2002
 Location
 Hastings, Sussex, England
 Posts
 67
 Thanks
 0
 Thanked 1 Time in 1 Post
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,

20070927, 21:48 #3
 Join Date
 Jul 2006
 Location
 Bangalore, India
 Posts
 180
 Thanks
 1
 Thanked 0 Times in 0 Posts
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

20070927, 22:44 #4
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 Posts
Re: IF & AND Condition (Excel 2003)
I'd create a pivot table based on the data table.

20070928, 06:10 #5
 Join Date
 Jul 2006
 Location
 Bangalore, India
 Posts
 180
 Thanks
 1
 Thanked 0 Times in 0 Posts
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

20070928, 07:13 #6
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 Posts
Re: IF & AND Condition (Excel 2003)
I don't understand  there is nothing to calculate a median of.

20070930, 10:26 #7
 Join Date
 Jul 2006
 Location
 Bangalore, India
 Posts
 180
 Thanks
 1
 Thanked 0 Times in 0 Posts
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

20070930, 13:06 #8
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: IF & AND Condition (Excel 2003)
All array formulas (confirm with ctrlshiftenter):
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

20070930, 13:17 #9
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 Posts
Re: IF & AND Condition (Excel 2003)
Try the attached version.

20070930, 13:32 #10
 Join Date
 Jul 2006
 Location
 Bangalore, India
 Posts
 180
 Thanks
 1
 Thanked 0 Times in 0 Posts
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