Automate Excel

Sub SumByCondition()

' Assign the worksheet to a variable

Dim ws As Worksheet

Set ws = ThisWorkbook.Sheets("Sheet1")

' Initialize the sum variable

Dim SumQ1 As Double

Dim SumQ2 As Double

' Define the last row of data

Dim lastRow As Long

lastRow = ws.Cells(Rows.Count, 2).End(xlUp).Row

' Loop through the data in column B

For i = 1 To lastRow

' Check if the value in column B equals 'Q1'. If so, add the corresponding value in column A to the sum

If ws.Cells(i, 2).Value = "Q1" Then

SumQ1 = SumQ1 + ws.Cells(i, 1).Value

' Check if the value in column B equals 'Q2'. If so, add the corresponding value in column A to the sum

ElseIf ws.Cells(i, 2).Value = "Q2" Then

SumQ2 = SumQ2 + ws.Cells(i, 1).Value

End If

Next i

' Output the sums to the debug window

[debug.print](/vba/debug-print-immediate-window) "Q1 Sum: " & SumQ1

Debug.Print "Q2 Sum: " & SumQ2

End Sub

• Description: This is a complex Excel formula that utilizes the INDEX, MATCH, COUNTIF, MAXIFS, and IFERROR functions to find the largest value (via MAXIFS) in range $C$2:$C$100 based on the condition in range $A$2:$A$100 equals the value in cell A2 from another list ($B$2:$B$100) without repeating values (via COUNTIF), and returns corresponding value from the second list; if there's an error (no match found), it returns "No Match" (via IFERROR).
• Steps:

1. The MAXIFS function, $C$2:$C$100=MAXIFS($C$2:$C$100, $A$2:$A$100, A2), will get the maximum value in range $C$2:$C$100 where the parallel value in range $A$2:$A$100 equals the value in cell A2.
2. The COUNTIF($D$1:D1, $B$2:$B$100)=0 function is to find unique values from range $B$2:$B$100 that have not appeared above the cell D1.
3. These two conditions are combined with a multiplication operator to create an array of TRUE/FALSE values (1 and 0, respectively). If both conditions are true (i.e., it's a unique maximum value), then the result is 1 (true); otherwise, the result is 0(false).
4. The MATCH function, MATCH(1, ... , 0)), finds the first true condition (1), which is the position of the matched cell.
5. The INDEX function, INDEX($B$2:$B$100, ...), uses the result of MATCH as the row_num argument to return the corresponding value from the range $B$2:$B$100.
6. Finally, the IFERROR function wraps the whole formula: If any error occurs during the execution of the formula (e.g., no match is found), it returns "No Match" as an error value.

• Additional Information:
• This formula may slow down Excel performance if used on large datasets due to its array nature.
• Each function and operation inside the formula has its own precedence. Please carefully observe the parentheses.
• The "$" is used for absolute referencing in Excel. This means the cell references are fixed and do not change when copied or filled down.

This formula sums all values in column A where the corresponding cell in column B equals "Q1". It uses the SUMIFS function, which allows you to add numbers in a range based on multiple criteria.

1. SUMIFS(A:A, B:B, "Q1"): The SUMIFS function adds all the numbers in column A where the corresponding value in column B is "Q1". The function has three arguments - range, criteria_range, and criteria. In this case, range is A:A (meaning all the cells in column A), criteria_range is B:B (all the cells in column B), and criteria is "Q1". The function checks every cell in column B to see if it contains "Q1", and if it does, it adds the corresponding cell from column A to the total sum.