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

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

Quote from crushdrinker06;769671

You said

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

As far as

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

If the different 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 I might be able to give you a more complete answer.

Display More