I am scraping a webpage that contains digits and associated states…I have have broken those digits to it lowest common multiple…in the first sheet of this spreadsheet…and in sheet I have filter those result and applied a series of calcution using eg array(if(a:a=””,,a:a*{12,13,37,101,111}))…the result are in ranges A5:f of that sheet. Subsequently I conditionally formatted the digits in columns I and k respectfully to highlight duplicates in the range a5:f. I would like to have the names and corresponding digits appear in or be generated and highlighed in the range a5:f ..(possibly m5:q also) could someone assist? Thanks in advance

# Tag: countifs

## google sheets – Using countifs with Index function and (OR) criteria

I’m using this formula to count rows that has either of the following text (SCD,SCA,Sickl).

First I indexed two columns to be one column (E & G)

Then I asked to count any row that has any one of the following texts (*SCD* or *SCA* or *sickl*). if the column has two of three of those texts, It will all return same count 1 as one row only.

```
=ArrayFormula(sum(countif(index(admissions!E9:E58 & admissions!G9:G58),{"*SCD*","*SCA*","*Sickl*"})))
```

https://docs.google.com/spreadsheets/d/1OUk3hUwL2CcePR4aKXU746DQTVdTVE5_lICpYSKBLpg/edit?usp=sharing

This is the link, the formula gives back a result of 6 while it should have given 4 only.

## Use of Countifs to check values from two different columns to match a criteria

I have a data with 3 tabs. Tab 1 has information to be analyzed in tab 3. So in tab 1 I have column J and K which have responses of either "yes" or "No" in one row. So if for example row 5 column J shows "Yes" while K shows No/ J"No" and K"Yes"/both J and K shows No, I term that as an error. If both J and K are Yes then I need not to count that. Which formular can I use.

## google sheets – Expand Countifs Arrayformula in rows and columns

In this google sheet

I have an array of Types and floors, The criteria rows and headers are auto populated by unique formulas.

I’m trying to create an array formula which will expand in the width (columns) and height (rows) infinitely, to lookup the count.

So instead of having an arrayformula in each cell in row 3, there will be just one in F3

Thanks,

## google sheets – Countifs with index match and importrange

I have two worksheets

https://docs.google.com/spreadsheets/d/141IcRU2RE_WmBGK4bMOI-_zyz1-hwU1TTylfbBz2pIE/edit?usp=sharing

https://docs.google.com/spreadsheets/d/1kGJaIu6leOzeXWyAyfaZsllMib8b8esOuDWYwzlxyYo/edit?usp=sharing

I am trying to count the number of instances each person has in each tab of sheet 2 and then import that total to sheet 1. Im unsure which formulas I should be using of if this is even possible. I’ve included a small description on each tab of sheet 2.

If someone could help me understand what I should be using to accomplish this I’d be grateful

## Microsoft Excel – Compress a COUNTIFS formula

Trying to help a friend with a really simple one `COUNITF/COUNTIFS`

Formula. Data is like:

The aim is to only count the data cells *Mike*, *John*, *Judy*. Current formula:

```
=COUNTIFS(A2:A13,"<>mike",A2:A13,"<>john",A2:A13,"<>judy")
```

This returns the correct value 3. The problem is that he has to exclude a lot more than three names. We would like to list them in the column **B.** and let the formula refer to the list (*otherwise the formula will be huge*). We tried it:

```
=SUM(COUNTIF(A2:A13,"<>B2:B4"))
```

both entered normally and entered array, but it gives 12!

Strangely, if we want to **include** rather than **exclude** then:

```
=SUM(COUNTIF(A2:A13,B2:B4))
```

works. I offered a VBA solution but it was rejected.

## vba – performance on countifs and sumifs

I'm trying to count on `SUMIFS`

and `COUNTIFS`

Data.

The output sheet looks like this:

I hid some irrelevant or sensitive data to make it easy to get to the point.

My code executes an array on this worksheet and calculates a few KPIs per day and user that I need from this worksheet:

The calculation is fairly simple, but it has a big impact on performance:

```
Option Explicit
Sub Test()
With Hoja4
Dim arrTotal As Variant: arrTotal = .UsedRange.Value
Dim Fecha As Double
Dim i As Long
For i = 2 To UBound(arrTotal)
Fecha = arrTotal(i, 1)
arrTotal(i, 13) = CalculaLlamadas(CStr(arrTotal(i, 2)), Fecha, 1, 4)
arrTotal(i, 14) = CalculaFCR(CStr(arrTotal(i, 2)), Fecha, 1, 4)
arrTotal(i, 15) = CalculaLlamadas(CStr(arrTotal(i, 2)), Fecha, 3, 5)
arrTotal(i, 16) = CalculaFCR(CStr(arrTotal(i, 2)), Fecha, 3, 5)
arrTotal(i, 17) = CalculaLlamadas(CStr(arrTotal(i, 2)), Fecha, 30, 6)
arrTotal(i, 18) = CalculaFCR(CStr(arrTotal(i, 2)), Fecha, 30, 6)
Next i
.UsedRange.Value = arrTotal
End With
End Sub
Private Function CalculaFCR(Agente As String, Fecha As Double, Dias As Byte, Columna As Byte) As Long
Dim FechaFin As Double: FechaFin = Fecha + Dias
With Hoja37
CalculaFCR = Application.SumIfs(.Columns(Columna), .Columns(1), Agente, _
.Columns(2), ">=" & Fecha, .Columns(2), "<=" & FechaFin)
End With
End Function
Private Function CalculaLlamadas(Agente As String, Fecha As Double, Dias As Byte, Columna As Byte) As Long
Dim FechaFin As Double: FechaFin = Fecha + Dias
With Hoja37
CalculaLlamadas = Application.CountIfs(.Range("A:A"), Agente, _
.Range("B:B"), ">=" & Fecha, .Columns(2), "<=" & FechaFin)
End With
CalculaLlamadas = LLamadas
End Function
```

As you can see in the code, the logic is pretty easy to calculate how often a user shows between two dates and get the sum of a column for that data.

I can't figure out how to do it faster. I also tried using an array to iterate over instead of using the countif and sumif, but it takes even longer because the input sheet contains 40 KB (one and a half months of data) of lines and it is likely that they will will be increased with the coming days.

Any suggestions on how you can improve performance for this task?

## google sheets – COUNTIFS with logical AND and extended criteria

Hello batch exchange community!

I am trying to create a presence board to keep track of the hours available and spent. I linked a demo sheet at the end of this post.

The Config tab defines roles and assigns a number of hours available to each person depending on their roles.

The "January" tab is a calendar in which everyone can enter their working hours (M, A or J, which each stand for morning, afternoon and all day (I'm actually French, M = Matin, A = Après-Midi & J =) journée))

Sometimes we have meetings that should not count as working hours. This is what column G is for:

– If "Y" is set, no time is entered for entries on this day

– If "M" is set, the time is only used for people who worked in the afternoon

– If "A" is set, the time is only used for people who worked in the morning

So far everything works well. What I'm trying to do is add different types of meetings associated with each role: Sometimes meetings only involve RP people or CSSCT people.

When such a meeting takes place, the working hours of people who do not have the appropriate role should only be increased. For example, if an RP meeting is taking place:

– RP people who enter time for this meeting should NOT increase their time spent

– CSSCT staff can enter their working hours during the meeting (ie they work alone but at the same time) and they MUST find that their working hours increase

As you can see, much of the magic on line 38 happens with a big fat formula that I don't seem to be able to tweak to do what I just described above. Some of the things I've tried so far are:

– script: I just can't have a sheet that doesn't update immediately

– Use the logical AND in the COUNTIFS function to handle complex conditions that don't seem to work

– use the IF function in COUNTIFS, but that doesn't seem to work either

– I have searched many forums, but apparently cannot find anything suitable for my application (array form, query …)

Does anyone have any insight into what might interest me to solve this problem?

Thank you in advance !

The demo sheet: https://docs.google.com/spreadsheets/d/1PY7pT4CrvtWEhZ64CGRKEItZV1W7vxBEiIpLX8LlnSU/edit?usp=sharing

## google sheets – COUNTIF / COUNTIFS – batch exchange of web applications

I am converting an Excel sheet to a Google sheet, but the formulas are not translated as hoped. Below is a formula that I use, which basically counts the people I work shift shifts a day.

```
=COUNTA(R7:R10)-SUM(COUNTIFS(R7:R10,{"*RB*","O","OUT","COMP","SICK",".O.","H","BH","PAT","LD","LL","BDAY","T","UL",".BH.","IND","VEG","SAUCE","FISH","HOTS","COLDS","*APP*","*CAN*","apsl","PASTRY","H&S","ECO","T/O","UNI"}))-$D12+R86
```

We use free-form words so people know what they are doing when they print the rota, but we can also use formulas to count employees who are not excluded from other activities.

So R & – R10 in this example is a section, so it first counts how many cells are filled (people in this section), and then removes those that are off-site or off-day (O) / (OUT) – Exclude disease, or on another section. Then we have a number (D12) that indicates the number of people we want to have in this section. R86 counts if there is a section in its array anywhere else in the table. For example, there is only one person in the "cold" section. We will enter a different section for another "cold" person so that we know the person will be supported on that day.

Could someone help translate this formula? or help me rewrite it. It works absolutely well on Excel.

## google sheets – Wrong and weird results when using COUNTIFs with Arrayformula

I have three functions to check if I have a stock (by seeing that column F is not empty). Then I want to check column AB to determine which market the stock comes from. In the first case, I would like to calculate the number of large cap companies in which I own shares.

However, my formulas seem to recalculate incorrectly. Sometimes they do not change at all when I add new stock to my portfolio. The following three functions are the functions that I use. (Remember, these are European delimiters, so the ";" should not be changed to "," for example.

```
=COUNTIFs(('ISK Investeringar'!$F$3:$F$103); "<>" ; ArrayFormula(vlookup(('ISK Investeringar'!$F$3:$F$103);sort({'ISK Investeringar'!$F$3:$F$103'ISK Investeringar'!$AB$3:$AB$103};2;false);2;false)); "Large Cap" )/D3
=(COUNTIFs(('ISK Investeringar'!$F$3:$F$103); "<>" ; ArrayFormula(vlookup(('ISK Investeringar'!$F$3:$F$103);sort({'ISK Investeringar'!$F$3:$F$103'ISK Investeringar'!$AB$3:$AB$103};2;false);2;false)); "Mid Cap") + COUNTIFs(('ISK Investeringar'!$F$3:$F$103); "<>" ; ArrayFormula(vlookup(('ISK Investeringar'!$F$3:$F$103);sort({'ISK Investeringar'!$F$3:$F$103'ISK Investeringar'!$AB$3:$AB$103};2;false);2;false)); "Small Cap"))/D3
=COUNTIFs(('ISK Investeringar'!$F$3:$F$103); "<>" ; ArrayFormula(vlookup(('ISK Investeringar'!$F$3:$F$103);sort({'ISK Investeringar'!$F$3:$F$103'ISK Investeringar'!$AB$3:$AB$103};2;false);2;false)); "First North" )/D3
```

With these 3 formulas and for example with 7 different stocks in the following markets:

Big cap

Big cap

Big cap

Big cap

ETF

Mid cap

First north

I get the following percentages:

71% large cap

14% mid cap

0% First North

Can anyone see what's wrong in this formula for the purpose described above? I have the same formula to see in how many different industries I own companies, and this feature works perfectly. I will share this feature below:

```
=COUNTIFs( UNIQUE('ISK Investeringar'!$C$3:$C$103); "<>"; ArrayFormula(vlookup(unique('ISK Investeringar'!$C$3:$C$103);sort({'ISK Investeringar'!$C$3:$C$103'ISK Investeringar'!$F$3:$F$103};2;false);2;false)); "<>" )
```

I'd like to know why my first 3 formulas do not work properly, but the last one.

thank you in advance