Results 1 to 10 of 10
  1. #1
    2 Star Lounger
    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
    Attached Files Attached Files

  2. #2
    Star Lounger
    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,

  3. #3
    2 Star Lounger
    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

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: IF & AND Condition (Excel 2003)

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

  5. #5
    2 Star Lounger
    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

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: IF & AND Condition (Excel 2003)

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

  7. #7
    2 Star Lounger
    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
    Attached Files Attached Files

  8. #8
    WS Lounge VIP sdckapr's Avatar
    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 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. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: IF & AND Condition (Excel 2003)

    Try the attached version.
    Attached Files Attached Files

  10. #10
    2 Star Lounger
    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

Posting Permissions

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