regexgoogle-sheetsfiltersumgoogle-sheets-formula
Sum variable number of columns with multiple criteria on multiple columns
Looking for some help on creating a formula (ideally Google Sheets compatible) that will allow me to define criteria range for multiple columns and define how many columns to sum.
For example, in the screenshot provided, I am interested in:
- Names A, B, and C
- Program X and Z
- Sum of the first two months (Jan and Feb)
- I would expect the result to give 1050, and if I change the Months criteria to 3, it would add in March as well and total to 550
Thanks!
Solution
use:
=SUM(FILTER(FILTER(C2:F10, COLUMN(C1:F1)-COLUMN(C1)<C12),
REGEXMATCH(A2:A10, TEXTJOIN("|", 1, A12:A15)),
REGEXMATCH(B2:B10, TEXTJOIN("|", 1, B12:B15))))
- How to test if a RegExp contains capturing groups in its definition?
- create regex to match format of 00:00:00 for duration (not time)
- regex for expression with one separator hyphen
- Replace variables in a formula with their definitions
- RegEx to differentiate chemical compound names from pattern
- Replace letters inside words that start with and end with a specific letter not occurring anywhere else with the same amount of a specific character
- open vimeo url in colorbox using jQuery filter
- Split address and numbers
- Pyspark Regular Expression add double quotes after comma
- In the regex world what's a flavor and which flavor does Java use?
- Problems with table extraction using pdfplumber - empty fields in large tables
- How do I pass a variable into regex with Node js?
- How do I use regex in a SQLite query?
- Python RegEx: match words in a string only if they are not preceded by a specific character
- Regular expression that ignores HTML entities
- Allow access to page only from certain referrer
- Regex to match string containing two names in any order
- Matching boot times from log using sed -E
- Validate Bangladeshi phone number with optional +88 or 01 preceeding 11 digits
- Using setCustomValidity stops input from validating
- Use dynamic (variable) string as regex pattern in dart
- Regular expression matching Android package name
- Replace commas not inside single quotes with an @ symbol
- Visual Studio Code Search and Replace with Regular Expressions
- Split string on commas not inside double quotes
- Extracting specific data from a string with regex and Powershell
- Parse datetime string in Ydm.His format
- Extract all words double wrapped in curly braces
- Parse dot-delimited video filename to extract show title, series number and episode number
- Split a string into words, preserving contractions but removing leading apostrophes from words