CountIFs with comparison of variable extracted value (LEFT Function)

[hijaz]

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)

 

[hijaz]

I asked about column AE, not N.

Sorry got mixed up with columns. But answer is that length will be variable from 1-3. See below

CVSS Base

5 (AV:N/AC:L/Au:N/C:P/I:N/A:N)

4.3 (AV:N/AC:M/Au:N/C:P/I:N/A:N)

6.4 (AV:N/AC:L/Au:N/C:P/I:P/A:N)

6.4 (AV:N/AC:L/Au:N/C:P/I:P/A:N)

2.6 (AV:N/AC:H/Au:N/C:P/I:N/A:N)

6.4 (AV:N/AC:L/Au:N/C:P/I:P/A:N)

4.3 (AV:N/AC:M/Au:N/C:N/I:P/A:N)

 

[Peter_SSs]

we dont know how many rows the sheet will have.

.. but do you have any idea of a limit? Are you likely to have more than 1,000,000 rows? Best to put some limit on it if you can to save calculation resources.

The adaptation of my earlier suggestion would be:

=COUNT(FILTER('All Vulns'!N2:N1000,(LEFT(0&'All Vulns'!AE2:AE1000,3)-6>=0)*('All Vulns'!C2:C1000="Obsolete")*('All Vulns'!N2:N1000>=4),""))

 

[hijaz]

.. but do you have any idea of a limit? Are you likely to have more than 1,000,000 rows? Best to put some limit on it if you can to save calculation resources.

The adaptation of my earlier suggestion would be:

=COUNT(FILTER('All Vulns'!N2:N1000,(LEFT(0&'All Vulns'!AE2:AE1000,3)-6>=0)*('All Vulns'!C2:C1000="Obsolete")*('All Vulns'!N2:N1000>=4),""))

We can use, but the idea is to use the reference of the column if there is any method. This is what I am trying to find.

 

[hijaz]

We can use, but the idea is to use the reference of the column if there is any method. This is what I am trying to find.

By the way, I tried 10000 columns, it gives error.

 

[hijaz]

Anybody with any ideas, I am stuck with this thing. Otherwise I will have to go to MACROS only because of this minor thing.

So somebody please help

 

[Peter_SSs]

By the way, I tried 10000 columns, it gives error.

Columns, or rows?

Please post the actual formula that you tried.

 

[hijaz]

Columns, or rows?

Please post the actual formula that you tried.

Sorry rows. I just changed the following highlighted number of rows from 1000 to 10000 and it gave error #N/A

=SUMPRODUCT(('All Vulns'!C2:C10000="Obsolete")*('All Vulns'!N2:N10000>=4)*(--LEFT("0"&'All Vulns'!AE2:AE10000,3)>=6))

 

[Phuoc]

Try this:

=SUM(IF(ISNUMBER(--LEFT("0"&'All Vulns'!AE2:AE10000,3)),('All Vulns'!C2:C10000="Obsolete")*('All Vulns'!N2:N10000>=4)*(--LEFT("0"&'All Vulns'!AE2:AE10000,3)>=6)))

Enter with Ctrl+Shift+Enter.

 

[Peter_SSs]

Sorry rows. I just changed the following highlighted number of rows from 1000 to 10000 and it gave error #N/A

=SUMPRODUCT(('All Vulns'!C2:C10000="Obsolete")*('All Vulns'!N2:N10000>=4)*(--LEFT("0"&'All Vulns'!AE2:AE10000,3)>=6))

Did you get the same problem if you tried the post #12 suggestion with 10,000 instead of 1,000?

 

[hijaz]

Try this:

=SUM(IF(ISNUMBER(--LEFT("0"&'All Vulns'!AE2:AE10000,3)),('All Vulns'!C2:C10000="Obsolete")*('All Vulns'!N2:N10000>=4)*(--LEFT("0"&'All Vulns'!AE2:AE10000,3)>=6)))

Enter with Ctrl+Shift+Enter.

Error #N/A. I decreased the rows from 10000 to 1000, then works. Sam story as PEter's formula.