Excel Countif

On sheet 1 is a column with ranges of amounts.

These are in text form.

-

On another sheet I want to count the number of cells that match the text in the column from sheet 1.

-

An example of an amount, saved as text, in sheet 1 is: < €250k

-

How do I do a countif on sheet 2 that is counting all the < €250k in the column from sheet 1?

-

Thanks.

smuggler.ie

on first sheet

=COUNTIF(Sheet2!A1:A120,"250")

Sheet2!A1:A120 ->> sheet and range to be accounted for

"250" ->> whatever text you have there to be accounted for

edit: swap sheets as it suites you

sundodger5

Maybe i am misreading what you are asking but if the values are currency in text format will that not be a problem if looking for values less than 250k in the example you gave? unless the value is "< €250k"?

smuggler.ie

hmm, i double checked with actual "<€250k" - darn thing wont take it. It does not like € symbol, take it away and it works then.

https://www.boards.ie/discussion/comment/118480208#Comment_118480208

the value is "< €250k".

Ill check this out later today, thanks Smuggler.

smuggler.ie

@9214

Edit: it wont work with "<" ,">", "<>", "=" in the sample i have provided above or results wont be correct/reliable - those being operators in the function itself are interpreted by function as criteria defining symbol.

I have tested bit modded version - seam to provide desired result and you can keep € or whatever (you can change range(sheet) part as needed).

A1:A10 ->> formatted as text, but even generic or currency made no different

Still, test it before put in production.

Note - cell A9 has: < space €250k - applied function doesn't.

Appreciate the help. Excel brings out the curiosity/stubbornness in a few of us:)

Spectral.Instance

With an array formula*, you can count the number of cells even when they have inconsistent spacing:-

*entered by pressing CTRL+Shift+Enter (instead of just Enter alone - this adds the {} which shouldn't be typed)

smuggler.ie

Good one.