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)
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
Sort by date Sort by votes
Hi, you may want to try this solution

1609901048188.png


Enter the following array formula in B1 by pressing Ctrl + Shift + Enter

=SUM(IF((IF(ISBLANK($A$1:$A$16),0,LEFT($A$1:$A$16,4)*1))>6,1,0))

Kind regards

Saba
 
Upvote 0
Welcome to the MrExcel board!

Depending on if you have the LET function with your Excel 365

21 01 06.xlsm
ABC
1LETNo LET
27.5 (AV:N/AC:L/PR:N/UI:N/S:U/C:H/I:N/A:N)77
36.5 (AV:N/AC:H/PR:N/UI:N/S:U/C:H/I:L/A:N)
46.5 (AV:N/AC:L/PR:N/UI:N/S:U/C:L/I:L/A:N)
56.5 (AV:N/AC:L/PR:N/UI:N/S:U/C:L/I:L/A:N)
6
76.5 (AV:N/AC:L/PR:N/UI:N/S:U/C:L/I:L/A:N)
85.3 (AV:N/AC:L/PR:N/UI:N/S:U/C:N/I:L/A:N)
9
10
117.3 (AV:N/AC:L/PR:N/UI:N/S:U/C:L/I:L/A:L)
126.5 (AV:N/AC:H/PR:N/UI:N/S:U/C:H/I:L/A:N)
135.3 (AV:N/AC:L/PR:N/UI:N/S:U/C:L/I:N/A:N)
145.9 (AV:N/AC:H/PR:N/UI:N/S:U/C:H/I:N/A:N)
Count If
Cell Formulas
RangeFormula
B2B2=LET(lft,--LEFT(0&A2:A14,FIND(" ",A2:A14&" ")),COUNT(FILTER(lft,lft>6,"")))
C2C2=COUNT(FILTER(--LEFT(0&A2:A14,FIND(" ",A2:A14&" ")),--LEFT(0&A2:A14,FIND(" ",A2:A14&" "))>6,""))
 
Upvote 0
Hi Saba and Peter,

The solutions works but sorry I could not mentioned the complete requirement.

I am actually trying to use the following formula to a sheet where I want to embed the requested formula to the below bold part of the formula. The objective is to get the count of the rows with following criteria

Criteria:
- Has value "Obsolete" in column C, and
- Has value 4 or greater in column N and
- In column AE, number should be extracted and compared if greater than 6.

How can I possibly achieve it.

=COUNTIFS('All Vulns'!C:C,"=Obsolete",'All Vulns'!N:N,">=4", 'All Vulns'!AE:AE, ">=6")

See below screenshot for reference.

1609905362766.png
 
Upvote 0
Also, are any numbers at the left of column AE always exactly 3 characters long?
 
Upvote 0
Please find below the required data.

And the number in the N column could be 1 digit also, means the length is variable.

OS StatusTypeSeverityCVSS3 Base
ObsoleteVuln
3​
7.5 (AV:N/AC:L/PR:N/UI:N/S:U/C:H/I:N/A:N)
ObsoleteVuln
3​
6.5 (AV:N/AC:H/PR:N/UI:N/S:U/C:H/I:L/A:N)
ObsoleteVuln
2​
6.5 (AV:N/AC:L/PR:N/UI:N/S:U/C:L/I:L/A:N)
ObsoleteVuln
2​
6.5 (AV:N/AC:L/PR:N/UI:N/S:U/C:L/I:L/A:N)
ObsoleteVuln
2​
ObsoleteVuln
2​
6.5 (AV:N/AC:L/PR:N/UI:N/S:U/C:L/I:L/A:N)
ObsoleteVuln
2​
5.3 (AV:N/AC:L/PR:N/UI:N/S:U/C:N/I:L/A:N)
ObsoleteVuln
4​
ObsoleteVuln
3​
ObsoleteVuln
3​
7.3 (AV:N/AC:L/PR:N/UI:N/S:U/C:L/I:L/A:L)
ObsoleteVuln
3​
6.5 (AV:N/AC:H/PR:N/UI:N/S:U/C:H/I:L/A:N)
ObsoleteVuln
3​
5.3 (AV:N/AC:L/PR:N/UI:N/S:U/C:L/I:N/A:N)
ObsoleteVuln
3​
5.9 (AV:N/AC:H/PR:N/UI:N/S:U/C:H/I:N/A:N)
ObsoleteVuln
3​
ObsoleteVuln
3​
ObsoleteVuln
2​
9.1 (AV:N/AC:L/PR:N/UI:N/S:U/C:H/I:H/A:N)
ObsoleteVuln
2​
5.3 (AV:N/AC:L/PR:N/UI:N/S:U/C:N/I:L/A:N)
ObsoleteVuln
2​
6.5 (AV:N/AC:L/PR:N/UI:N/S:U/C:L/I:L/A:N)
ObsoleteVuln
2​
ObsoleteVuln
2​
ObsoleteVuln
2​
6.5 (AV:N/AC:L/PR:N/UI:N/S:U/C:L/I:L/A:N)
ObsoleteVuln
2​
6.5 (AV:N/AC:L/PR:N/UI:N/S:U/C:L/I:L/A:N)
ObsoletePractice
3​
ObsoletePractice
3​
ObsoletePractice
2​
ObsoletePractice
2​
ObsoletePractice
2​
ObsoletePractice
2​
Extended SupportVuln
4​
7.4 (AV:N/AC:H/PR:N/UI:N/S:U/C:H/I:H/A:N)
Extended SupportVuln
3​
7.5 (AV:N/AC:L/PR:N/UI:N/S:U/C:H/I:N/A:N)
Extended SupportVuln
2​
6.5 (AV:N/AC:L/PR:N/UI:N/S:U/C:L/I:L/A:N)
Extended SupportVuln
2​
5.3 (AV:N/AC:L/PR:N/UI:N/S:U/C:N/I:L/A:N)
Extended SupportVuln
1​
5.3 (AV:N/AC:L/PR:N/UI:N/S:U/C:L/I:N/A:N)
 
Upvote 0
Try this:

=SUMPRODUCT(('All Vulns'!C2:C1000="Obsolete")*('All Vulns'!N2:N1000>=4)*(--LEFT("0"&'All Vulns'!AE2:AE1000,3)>=6))
 
Upvote 0
Solution
Phuoc said:
Try this:

=SUMPRODUCT(('All Vulns'!C2:C1000="Obsolete")*('All Vulns'!N2:N1000>=4)*(--LEFT("0"&'All Vulns'!AE2:AE1000,3)>=6))
Click to expand...
Thanks Puoc, it works.

But one more problem. As part of the automation, we dont know how many rows the sheet will have. So we cant use the exact number of rows like 'All Vulns'!C2:C1000 is not acceptable for my situation. I tried using 'All Vulns'!C:C but it is acceptable only for CountIFs formula.

Any suggestions to overcome this issue?
 
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
887
NdNoviceHlp
NdNoviceHlp
A
  • Question
Chart data with multiple blank cells
Replies
0
Views
649
artz
A
Z
  • Question
Creating 2 lists by extracting unique values but values in each list are based off each other
Replies
1
Views
647
Zacharygolf
Z
A
  • Question
Highlighting cells if the value is >x in Excel 2003
Replies
2
Views
751
Albafly
A

Forum statistics

Threads
1,215,194
Messages
6,123,569
Members
449,108
Latest member
rache47

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