Excel COUNTIF every 2nd column within a year
I want to count certain days within a year by value, meaning all the aaa and bbb as shown below, but I cannot figure out how to incorporate that in the formula =COUNTIFS(A1:D20;">="&DATEVALUE("01/01/"&$E$24);A1:D20;"<="&DATEVALUE("31/12/"&E24))
Solution
Assuming no Excel-version constraints
=SUM((YEAR(MAP(A1:C20,LAMBDA(n,N(n))))=E24)*((B1:D20="aaa")+(B1:D20="bbb")))
If you have an older version of Excel then you can use this version
=SUM((YEAR(IF(ISNUMBER(A1:C20),A1:C20))=E24)*((B1:D20="aaa")+(B1:D20="bbb")))
as an array formula.
- Excel VBA - Skip sub or end sub IF Statement
- What's the difference between "Unpivot Columns" and "Unpivot Only Selected Columns" in Power Query?
- Find all matches that use the 4 digits in sheet1 to find the 10 digit phone numbers by matching the last 4 numbers in sheet2
- Excel VBA- Loop Through Target Cells in a Table for Tracking Changes
- VBA Concatenate 2 columns without loop
- Excel: What should iferror return in order to avoid #value! error
- Excel VLOOKUP for 3 columns
- Excel: How to check if a value that meets the specified criteria exists in a lookup table?
- Showing like number format of two numbers separated by dashes in Excel
- Excel distinct count with conditions
- Different background colors for even and odd rows
- excel adding 1 on all alphanumeric string
- For an Excel Formula's variable taking a Range, input row number by the result of a formula
- how to change the text number automatically in excel without retype the formula?
- Excel: Find last value in an array
- Combining formulas
- How to skip calculating a cell if its left cell has an error value
- Power Query - splitting column
- Getting "At least one sheet must be visible" when trying to open excel spreadsheet. What to do?
- Update cells in a range based on cells in a range of a different dimensions, using VBA
- Timer on user form in Excel VBA
- Generating CSV file for Excel, how to have a newline inside a value
- Circular Reference in Dynamic Array Formula
- Excel array formula that references previously calculated values within the array
- Detect bold texts in a cell and add ";" before those texts
- Convert an array of bytes into a string?
- Deleting rows from Excel sheet based on the values in column A and Column H
- Dir Function to find file
- How to add corresponding values to a different sheet with duplicate values (while keeping the duplicates intact)?
- Find row when Cumulative sum reaches certain value with condition