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

 

[gdrowell]

Ok, . . . Last question. What if the Cells in Column F displayed numbers from another formula. Then the ISNUMBER won't work. Is there another way around that if the number is given by formula? . . . You are very helpful

 

[Peter_SSs]

Ok, . . . Last question. What if the Cells in Column F displayed numbers from another formula. Then the ISNUMBER won't work. Is there another way around that if the number is given by formula? . . . You are very helpful

The formula I gave works fine with formulas in column F if they return numbers. I suspect that your formulas in column F are not returning numbers but text. For example, "7" instead of 7.

Could we see one of the formulas from column as that may well be the best place to resolve the problem?

On the other hand, if you really want to leave those values in column F as text, you could change my formula to

=SUMPRODUCT(--ISNUMBER(MATCH(F2:F11+0,A9:A11,0)))

 

[gdrowell]

It looks like i had a little USER error . . . Both of you Formula work fine. I just had fat fingers and entered an ' to it by accident. That why it was returning a value. All is well now. Thank you again

 

[gdrowell]

Wasn't*

 

[Peter_SSs]

All is well now. Thank you again

No problem. Glad you got it figured out.

 

[gdrowell]

So,

=SUMPRODUCT(--ISNUMBER(MATCH(F2:F11+0,A9:A11,0)))

will return a value, even if the values are 0. How do i get this to not include an number <0. If A9:A11 is blank Cells, and F2:F11 have 0 in cells. I don't want to calculate any cell in F2:F11 that is >0, and I don't want to consider any Blank Spaces in A9:A11, that will make this formula use the blanks as zeros. Thank you in advance for the assistance.

 

[Peter_SSs]

Can you please clarify whether F2:F11 contains numbers or text that looks like numbers? If they are text that look like numbers and you said earlier that they came from formulas, why don't you change the formulas to return actual numbers? Then all the arithmetic become much simpler.
Could we see at least one of the formulas from that range?


Same questions for A9:A11

 

[gdrowell]

Ok.

[TABLE="class: grid, width: 75, align: center"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]9[/TD]
[/TR]
</tbody>[/TABLE]

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

WILL EQUAL 9

BUT I WANT IT SHOW 5 (BECAUSE THAT THE NUM OF TIMES 1, 2, 3 APPEAR COLLECTIVELY)

THE "9" COUNTS THE BLANKS AS ZEROS, BECAUSE ZEROS APPEAR IN A1:A9. I WANT EXCLUDE THE ZEROS WHEN A ZERO BECOMES PRESENT.

i HOPE I WAS CLEAR. i TRIED TO ATTACH MY ORIGINAL FILE, BUT IT WON'T COPY. iF YOU WOULD LIKE TO EXCHANGE EMAILS, I CAN SEND YOU THE WORKBOOK VIA EMAIL.

THANK YOU AGAIN

 

[gdrowell]

All cells are number formatted

 

[gdrowell]

cell f9=sumproduct(--isnumber(match(a1:a9+0,d1:d9,0)))