# 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!