Help Needed with simplifying

[gdrowell]

Is there a way to use the Countif function (or any function) to look up a range of criteria?

Example:

If i had a range of numbers (with repeating values), I would want to know How many times a range of numbers appear.

Right now I use =COUNTIF(A1:A100, "20"), if i wanted to see how many times "20" appears.

What i am looking for is a Function equation that can lookup multiple numbers in a range, then you the COUNT function to give me a total number of appearance ( i.e. How many time numbers 20-50, appear in the range of numbers)


Any help would be appreciated

 

[Peter_SSs]

CELL F9=SUMPRODUCT(--ISNUMBER(MATCH(A1:A9+0,D1:D9,0)))

WILL EQUAL 9

It doesn't for me, see below, but you haven't answered my questions and your data and layout seem to have changed significantly.

We started off looking at 3 cells only (A9:A11) and were counting how many times those numbers appeared in F2:F11 (refer to your formula in post 7)
Now we seem to have numbers in A1:A9 and I presume what was in column F is now in column D?

I'm now also unsure of exactly what you are trying to count and where. Hard to follow when things keep changing.

BUT I WANT IT SHOW 5

Can you explain clearly in words exactly how you come up with that number? On the face of it, it could simply be how may cells in column D contain numbers.

Excel Workbook
	A	B	C	D	E	F
1	1			1
2	2			1
3	3			2
4	4			2
5	5			3
6	0
7	0
8	6
9	7					3
Counting (3)

You have told me that all cells are number formatted. Unfortunately that doesn't tell me if the values in them are numbers or text as you can still have text in a cell that is formatted as number.
If you put these in two vacant cells, what do they return?
=ISNUMBER(A3)
=ISNUMBER(D3)


In post 11 you indicated that the values in column F (presumably now column D) came from formulas & in post 17 I asked if we could see one of those formulas but you haven't provided that.
Do the column A values also come from formulas? If so, could we also see one of those formulas?

i TRIED TO ATTACH MY ORIGINAL FILE

The forum does not allow file attachments. However you can give copyable data and formulas like I have above. There are several ways to do that & my signature block below has a link with those options.

iF YOU WOULD LIKE TO EXCHANGE EMAILS, I CAN SEND YOU THE WORKBOOK VIA EMAIL.

That is against the Forum Rules which you should have a read of, particularly number 4 about this issue.

 

[gdrowell]

My sincerest apology. I did not mean to violate. I have fully read the rules, and will not violate again. . . .

Thank you so very much for your assistance. I do apologize for being confusing as well

When i get back to my workbook, ill send the other formulas

Thanks agaih

 

[gdrowell]

ANOTHER USER ERROR (DOH!!!)

After looking at your example again, I see that i had the array ranges backwards in my workbook from what i posted.

I had

Spreadsheet Formulas

Cell Formula F9 =SUMPRODUCT(--ISNUMBER(MATCH(D1:D9+0,A1:A9,0))) <tbody> </tbody>

<tbody>
</tbody>

This gave me 9, because it counted the Blanks as zeros. . . .

After changing it around, i got the correct value i was looking for.

 

[Peter_SSs]

After changing it around, i got the correct value i was looking for.

Glad you got it sorted in the end.

 

[gdrowell]

Glad you got it sorted in the end.

Thank you kindly