## duplicate – How could I use vlookup and countifs function to match data in google sheets?

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

Posted on Categories Articles

## 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*"})))
``````

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

Posted on Categories Articles

## 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.

Posted on Categories Articles

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

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,

Posted on Categories Articles

## google sheets – Countifs with index match and importrange

I have two worksheets

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

Posted on Categories Articles

## 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.

Posted on Categories Articles

## 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
.Range("B:B"), ">=" & Fecha, .Columns(2), "<=" & FechaFin)
End With

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?

Posted on Categories Articles

## 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.

Posted on Categories Articles

## 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.