Countif or Array Formula

  • Hello,


    I hope my query is a very easy one to create a solution.


    I have a column of data. The data is not a number but a series of text from '011-020 & maybe 5 other range(s)/criteria of similar text, which would be in the same formula


    I would like a countif or array formula that results in the text "Cleaning" to be created on an adjacent column if the cell contains any of the text in that range or blank as result if not


    I than have a balance $ say 500.00 (in another cell) that I want to be reduced if that criteria above is valid/true. This balance is in another cell & there is another calculation result being conducted that gives a total on another column that this total would subtract from say column 'e' with a total amount used of $300.00 (this total is based another formula in the workbook), so the total based on the criteria above would now be showing as $200.00 balance


    To add another criteria to this, there maybe five separate totals which are consisting of 5 difference individuals, & would setup within the above formula another criteria such as person A, B, C, D & E to be validated on as well as the item number, criteria & category. The categories maybe 'cleaning, crowns, x-rays, dentures, etc


    Thank you



    [TABLE="width: 949"]

    [tr]


    [td]

    Quantity

    [/td]


    [td]

    Item number(s)

    [/td]


    [td]

    Code category

    [/td]


    [td]

    Code family

    [/td]


    [td]

    Code person

    [/td]


    [td]

    Code benefit

    [/td]


    [td]

    Item quote

    [/td]


    [td]

    Item benefit

    [/td]


    [td]

    Total benefit

    [/td]


    [td]

    Conditions of benefit

    [/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    1.00

    [/td]


    [td]

    011

    [/td]


    [td]

    Scaling

    [/td]


    [td][/td]


    [td]

    A

    [/td]


    [td]

    2

    [/td]


    [td]

    $ 41.00

    [/td]


    [td]

    $ 30.00

    [/td]


    [td]

    $ 30.00

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td]

    A

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td]

    $ -

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td]

    $ -

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td]

    $ -

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td]

    $ -

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td]

    $ -

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td]

    $ -

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td]

    $ -

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td]

    $ -

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td]

    $ -

    [/td]


    [td][/td]


    [td]

    Balance from WHICS

    [/td]


    [td]

    Balance (after benefits)

    [/td]


    [/tr]


    [tr]


    [td][/td]


    [td]

    Total

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [TD="align: right"]$41.00[/TD]

    [td][/td]


    [TD="align: right"]$30.00[/TD]

    [td]

    A Limits(s) remaining (

    [/td]


    [td]

    $ 300.00

    [/td]


    [td]

    $ 270.00

    [/td]


    [/tr]


    [tr]


    [td][/td]


    [td]

    Lump sum

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td]

    B Limits(s) remaining (

    [/td]


    [td]

    $ -

    [/td]


    [td][/td]


    [/tr]


    [tr]


    [td][/td]


    [TD="colspan: 6"]Percentage return[/TD]
    [TD="colspan: 2"]73%[/TD]

    [td]

    C Limits(s) remaining (

    [/td]


    [td]

    $ -

    [/td]


    [td][/td]


    [/tr]


    [tr]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td]

    D Limits(s) remaining (

    [/td]


    [td]

    $ -

    [/td]


    [td][/td]


    [/tr]


    [tr]


    [td][/td]


    [td]

    OOP

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [TD="colspan: 2"]$11.00[/TD]

    [td][/td]


    [td]

    E Limits(s) remaining (

    [/td]


    [td]

    $ -

    [/td]


    [td][/td]


    [/tr]


    [tr]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td]

    F Limits(s) remaining family

    [/td]


    [td]

    $ 500.00

    [/td]


    [td]

    Not applicable

    [/td]


    [/tr]


    [/TABLE]

  • Re: Countif or Array Formula


    You said

    Quote

    I would like a countif or array formula that results in the text "Cleaning" to be created on an adjacent column if the cell contains any of the text in that range or blank as result if not


    Do you mean that if the cell is blank then the adjacent cell would be blank else put "Cleaning"? What would be


    Quote

    Quote

    maybe 5 other range(s)/criteria of similar text, which would be in the same formula


    As far as



    Quote

    Quote

    I than have a balance $ say 500.00 (in another cell) that I want to be reduced if that criteria above is valid/true. This balance is in another cell & there is another calculation result being conducted that gives a total on another column that this total would subtract from say column 'e' with a total amount used of $300.00 (this total is based another formula in the workbook), so the total based on the criteria above would now be showing as $200.00 balance


    You could use the formula =if(CleaningCell = "Cleaning", TotalCell - Reduction, Total Cell)


    If the different

    Quote

    Quote

    cleaning, crowns, x-rays, dentures, etc

    have different reduction amounts then you could set up a table with the different reduction amounts and change the above formula to nested ifs.


    =if(CleaningCell = "Cleaning", TotalCell - CleaningReduction, if(CleaningCell = "x-rays", TotalCell - X-raysReduction,if(CleaningCell = "dentures", TotalCell - DenturesReduction,if(CleaningCell = "etc", TotalCell - EtcReduction,Total Cell))))


    I hope this helps, and if you can give more clarification to the

    Quote

    Quote

    maybe 5 other range(s)/criteria of similar text, which would be in the same formula

    I might be able to give you a more complete answer.

  • Re: Countif or Array Formula


    Hello & Thank you


    Appreciate the last part of the formula, that was very helpful.


    My main question though that I think may have been overlooked is the following:


    Okay a few examples


    I have four (4) ranges as follows:
    These ranges are all text based as although they are a number there is a 'zero' at the front & I don't know how to tell excel to treat them as a number but having a leading zero
    011-086 category "Diagnostic"
    511-597 category "Restorative"
    111-171 category "Preventative"
    911-972 category "General"


    What formula would I use to validate against the applicable category against that FULL range of numbers?


    If they are to stay as numbers, how do I tell excel I want a leading zero? Do I have to leave the number as text?


    Thanks again







  • Re: Countif or Array Formula


    As far as storing your codes as number. You can use https://support.office.com/en-…e1-4985-842f-5dfa51f85fe7 which shows how to do it.
    Unless you plan on manipulating the numbers, such as 011 minus 2, there is no real reason to stpre them as numbers


    Personally I would just leave them as text. then use formulas pertaining to text. For example

    Code
    =IF(B2=LEFT($Q$13,3),$R$13,IF(B2=LEFT($Q$14,3),$R$14,IF(B2=LEFT($Q$15,3),$R$15,IF(B2=LEFT($Q$16,3),$R$16,""))))


    where the B column is the item number you type in, Q column is the full seven digit item number in a reference table, and R column is the code category in a reference table.


    If you were to input the full seven digit item number into the B column, or change the item number in the reference table to only have three digits, then the formula would be

    Code
    =IF(B3=$Q$13,$R$13,IF(B3=$Q$14,$R$14,IF(B3=$Q$15,$R$15,IF(B3=$Q$16,$R$16,""))))


    I hope this is what you are looking for. I have also attached a sample workbook with the files above.


    forum.ozgrid.com/index.php?attachment/68935/

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!