Ask Experts Questions for FREE Help !
Ask
    miss-java's Avatar
    miss-java Posts: 4, Reputation: 1
    New Member
    		  
    #1

    Mar 22, 2007, 10:32 AM
    How could I put 2 conditions with COUNT IF Function and NESTED IF in Excel model
    Hi,
    I am a new member, I hope you could help me to complete my assignment tonight,
    I am stock with "Count IF" function in Microsoft Excel 2003

    How could I write the function with this commands:
    Count if the Tax which is (E15) not equal to 0, then I want to use a nested if with these calculations:
    =IF(C15<=1300,"&#163;500",IF(C15>5000,"&#163;2000" ,IF(C15>3000,"&#163;2500",IF(C15>=2000,"&#163;1500 ",IF(C15>=1400,"&#163;550")))))

    How can I put these tow conditions together?

    --------------------
    I came up with this, but Excel refuse it because it's wrong:
    =countif(E15="17.5%",NO,If((C15<=1300,"&#163;500", IF(C15>5000,"&#163;2000",IF(C15>3000,"&#163;2500", IF(C15>=2000,"&#163;1500",IF(C15>=1400,"&#163;550" ))))))




    Help me please :(
    asterisk_man's Avatar
    asterisk_man Posts: 476, Reputation: 32
    Full Member
    		  
    #2

    Mar 22, 2007, 10:38 AM
    Not sure what you want exactly.
    Where'd the 17.5 come from? Where'd the NO come from?
    Can you post the exact assignment please?
    ScottGem's Avatar
    ScottGem Posts: 64,966, Reputation: 6056
    Computer Expert and Renaissance Man
    		  
    #3

    Mar 22, 2007, 10:53 AM
    The assignment is not clear at all. CountIF counts the number of cells in a range that match a value.

    If you want a certain calculated value to appear in a cell based on the value of a nother cell, the nested Ifs are the right approach. Maybe:

    =IF(E15<>0, 175, IF(C15<=1300,500,IF(C15>5000,2000,IF(C15>30 00,2500,IF(C15>=2000,1500,IF(C15>=1400,550))))))

    You don't want to uses strings, you want to use numbers and format them for currency.
    miss-java's Avatar
    miss-java Posts: 4, Reputation: 1
    New Member
    		  
    #4

    Mar 22, 2007, 01:15 PM
    Sorry, for not giving more details in the first time, I am a student and my second language is english, so I hope that you become peitent with me.

    This is how my Excel model looks like,

    In the image, I highlighted the tow columns that I want to consider. The column that I want to put Count IF function is labeled (loan price)
    As you see, some cars are not available in the Gallery, so I put (coming soon), if you follow the row you will notice that there's no tax for that car because the car is not yet in the gallery, so (Loan price ) cloumn should show (0)
    If you could help me to sort the function out to be able to put the same criteria for (insurance),( Actual monthly price), (VAT) columns because they should show (0) as well.


    So how could I manage to say:
    If the car is not available Or (the TAX is 0) ,then the loan price should be a Zero , if the Tax is 17.5% then the loan price should be as the amount giving for each car model.

    In the other way ,

    Count the loan price as giving if the Tax is not 0


    Hope that you get me this time, and sorry for my english

    Miss Java
    ScottGem's Avatar
    ScottGem Posts: 64,966, Reputation: 6056
    Computer Expert and Renaissance Man
    		  
    #5

    Mar 22, 2007, 01:16 PM
    Did you try the function as I posted it earlier?
    miss-java's Avatar
    miss-java Posts: 4, Reputation: 1
    New Member
    		  
    #6

    Mar 22, 2007, 02:04 PM
    Yes, but how can I use COUNT IF instead NESTED IF
    ScottGem's Avatar
    ScottGem Posts: 64,966, Reputation: 6056
    Computer Expert and Renaissance Man
    		  
    #7

    Mar 22, 2007, 05:38 PM
    Quote Originally Posted by miss-java
    yes, but how can i use COUNT IF instead NESTED IF
    Why are using CountIF?
Not your question? Ask your question View similar questions
 

Question Tools Search this Question
Search this Question:

Advanced Search

Add your answer here.


Check out some similar questions!

Kenmore elite model quiet guard 7 model 665. [ 12 Answers ]

Six month old dishwasher. Worked fine until today - won't start. LIghts are on and control panel seems to be working OK. The "Clean" indicator is flashing in intervals of 7 flashes and then pauses. When I select the "Start" button it flashes red 3 times and then nothing. The "Drain/Cancel"...

Excel count function [ 12 Answers ]

I am trying to count a large group of cells that are colored depending on the data. For example: I want to count the number of red cells which are faults. I tried the countif but not sure how to set the criteria. Thanks for any help.

Excel Count Function [ 4 Answers ]

In November 2004 Leif 5233 wrote the following regarding counting cells highlighted in a certain colour: ------------------ Create this function: Function CountCol(SumRange As Range, intColor As Integer) As Integer Dim I As Integer Dim Cell As Range Set SumRange =...

How to remove a character using Excel function [ 3 Answers ]

Could anyone know if there is any Excel function that I can use to remove a certain character from a text string, e.g. 1). Remove space from "acurve is what" 2). Remove "-" from "acurve-is-what" 3). Remove "'" from "acurve'is" And so on. Thanks a lot!

View more questions Search