CountIFs with comparison of variable extracted value (LEFT Function)

H

hijaz

New Member
Joined
Jan 5, 2021
Messages
13
Office Version
  1. 365
Platform
  1. Windows
  2. Web
Hi,

I am new here and have run into a problem with Excel.

I have the following values in a column. I want to first extract the numerical value present before the space (i.e. 7.5 in first cell) in each cell then show me the count of the cells having that value greater than say "6". So that in below data's case, the answer should be "7". Please help me finding any solution to it. Remember I need this to be done in a single cell, as this is a part of automation.

I am trying countifs but it is not making any sense.

7.5 (AV:N/AC:L/PR:N/UI:N/S:U/C:H/I:N/A:N)
6.5 (AV:N/AC:H/PR:N/UI:N/S:U/C:H/I:L/A:N)
6.5 (AV:N/AC:L/PR:N/UI:N/S:U/C:L/I:L/A:N)
6.5 (AV:N/AC:L/PR:N/UI:N/S:U/C:L/I:L/A:N)

6.5 (AV:N/AC:L/PR:N/UI:N/S:U/C:L/I:L/A:N)
5.3 (AV:N/AC:L/PR:N/UI:N/S:U/C:N/I:L/A:N)


7.3 (AV:N/AC:L/PR:N/UI:N/S:U/C:L/I:L/A:L)
6.5 (AV:N/AC:H/PR:N/UI:N/S:U/C:H/I:L/A:N)
5.3 (AV:N/AC:L/PR:N/UI:N/S:U/C:L/I:N/A:N)
5.9 (AV:N/AC:H/PR:N/UI:N/S:U/C:H/I:N/A:N)
 
Sort by date Sort by votes
Y
Peter_SSs said:
Did you get the same problem if you tried the post #12 suggestion with 10,000 instead of 1,000?
Click to expand...
Yes, same error on increasing the no. of rows. Actually only CountIFs formula accommodates the full column (all rows). I guess these sumprod and sum(if...) formulas have limitation on no of rows.

Any other idea?
 
Upvote 0

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
hijaz said:
I guess these sumprod and sum(if...) formulas have limitation on no of rows.
Click to expand...
No, none of them have limitations on rows.


hijaz said:
Yes, same error on increasing the no. of rows.
Click to expand...
You say the same error (#N/A) for the post #12 formula. It is not possible for that formula to return #N/A simply by increasing rows as it returns a count. Even if the calculations within the formula return errors, the COUNT function will simply return 0. Small example to demonstrate

hijaz sample.xlsm
CNAEAFAG
2#N/Aabc#VALUE!0
3Obsolete#DIV/0!6.5 (AV:N/AC:H/PR:N/UI:N/S:U/C:H/I:L/A:N)
All Vulns
Cell Formulas
RangeFormula
C2C2=NA()
AE2AE2=FIND("a","b")
AG2AG2=COUNT(FILTER('All Vulns'!N2:N3,(LEFT(0&'All Vulns'!AE2:AE3,3)-6>=0)*('All Vulns'!C2:C3="Obsolete")*('All Vulns'!N2:N3>=4),""))
N3N3=1/0



hijaz said:
Any other idea?
Click to expand...
Yes. You possibly have an error value or some particular data below row 1,000 that is causing the formulas to error.

I have tested the formulas from posts 12, 17 and 18 with 100,000 rows (that is, ten times more than you have tried) and they all returned the correct results (I checked the results manually by filtering the 'All Vulns' data by the three columns and confirmed the number of visible rows.

Here they are, using 100,000 rows in the formulas:
hijaz.xlsm
KLM
1Post 12Post 17Post 18
219,27219,27219,272
Count If
Cell Formulas
RangeFormula
K2K2=COUNT(FILTER('All Vulns'!N2:N100000,(LEFT(0&'All Vulns'!AE2:AE100000,3)-6>=0)*('All Vulns'!C2:C100000="Obsolete")*('All Vulns'!N2:N100000>=4),""))
L2L2=SUMPRODUCT(('All Vulns'!C2:C100000="Obsolete")*('All Vulns'!N2:N100000>=4)*(--LEFT("0"&'All Vulns'!AE2:AE100000,3)>=6))
M2M2=SUM(IF(ISNUMBER(--LEFT("0"&'All Vulns'!AE2:AE100000,3)),('All Vulns'!C2:C100000="Obsolete")*('All Vulns'!N2:N100000>=4)*(--LEFT("0"&'All Vulns'!AE2:AE100000,3)>=6)))
Press CTRL+SHIFT+ENTER to enter array formulas.
 
Last edited:
Upvote 0
Peter_SSs said:
No, none of them have limitations on rows.



You say the same error (#N/A) for the post #12 formula. It is not possible for that formula to return #N/A simply by increasing rows as it returns a count. Even if the calculations within the formula return errors, the COUNT function will simply return 0. Small example to demonstrate

hijaz sample.xlsm
CNAEAFAG
2#N/Aabc#VALUE!0
3Obsolete#DIV/0!6.5 (AV:N/AC:H/PR:N/UI:N/S:U/C:H/I:L/A:N)
All Vulns
Cell Formulas
RangeFormula
C2C2=NA()
AE2AE2=FIND("a","b")
AG2AG2=COUNT(FILTER('All Vulns'!N2:N3,(LEFT(0&'All Vulns'!AE2:AE3,3)-6>=0)*('All Vulns'!C2:C3="Obsolete")*('All Vulns'!N2:N3>=4),""))
N3N3=1/0




Yes. You possibly have an error value or some particular data below row 1,000 that is causing the formulas to error.

I have tested the formulas from posts 12, 17 and 18 with 100,000 rows (that is, ten times more than you have tried) and they all returned the correct results (I checked the results manually by filtering the 'All Vulns' data by the three columns and confirmed the number of visible rows.

Here they are, using 100,000 rows in the formulas:
hijaz.xlsm
KLM
1Post 12Post 17Post 18
219,27219,27219,272
Count If
Cell Formulas
RangeFormula
K2K2=COUNT(FILTER('All Vulns'!N2:N100000,(LEFT(0&'All Vulns'!AE2:AE100000,3)-6>=0)*('All Vulns'!C2:C100000="Obsolete")*('All Vulns'!N2:N100000>=4),""))
L2L2=SUMPRODUCT(('All Vulns'!C2:C100000="Obsolete")*('All Vulns'!N2:N100000>=4)*(--LEFT("0"&'All Vulns'!AE2:AE100000,3)>=6))
M2M2=SUM(IF(ISNUMBER(--LEFT("0"&'All Vulns'!AE2:AE100000,3)),('All Vulns'!C2:C100000="Obsolete")*('All Vulns'!N2:N100000>=4)*(--LEFT("0"&'All Vulns'!AE2:AE100000,3)>=6)))
Press CTRL+SHIFT+ENTER to enter array formulas.
Click to expand...


Sorry mate my bad. The formula is correct but I guess we need to do exceptional handling for the cells as well, because column C is coming through VLOOKUP and might have some #NA values as well. This error was coming on row 3518 where this value was coming. Can you add exception handling to ignore such line and continue the course.

below is sample data in Column C. The Column N and AE can either have empty value or will have the values I mentioned previously. So exception handling needs to be done only on Column C.

Active
Active
Active
Active
Active
Active
#N/A​
#N/A​
Obsolete
Obsolete
Obsolete
 
Upvote 0
I have updated the formula as below, by adding exception handling for Column C. Thanks guys for your quick help and I also learned new functions and their uses.

=SUMPRODUCT(IFNA(('All Vulns'!C2:C100000="Obsolete"),0)*('All Vulns'!N2:N100000>=4)*(--LEFT("0"&'All Vulns'!AE2:AE100000,3)>=6))
 
Upvote 0
Try
Excel Formula: 
=COUNT(FILTER('All Vulns'!N2:N10000,(LEFT(0&'All Vulns'!AE2:AE10000,3)-6>=0)*(IFNA('All Vulns'!C2:C10000="Obsolete",))*('All Vulns'!N2:N10000>=4),""))
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Solved
Create Function that Calculates a Value Relative to Original Price and Also Constructs a Price Ladder
2
Replies
10
Views
876
NdNoviceHlp
NdNoviceHlp
A
  • Question
Chart data with multiple blank cells
Replies
0
Views
646
artz
A
Z
  • Question
Creating 2 lists by extracting unique values but values in each list are based off each other
Replies
1
Views
643
Zacharygolf
Z
A
  • Question
Highlighting cells if the value is >x in Excel 2003
Replies
2
Views
749
Albafly
A

Forum statistics

Threads
1,215,011
Messages
6,122,677
Members
449,092
Latest member
tayo4dgacorbanget

Share this page

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