Help Needed with simplifying

gdrowell

New Member
Joined
Dec 13, 2017
Messages
40
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
 
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
 
Upvote 0

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
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)))
 
Last edited:
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,223,903
Messages
6,175,284
Members
452,630
Latest member
OdubiYouth

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top