How can I SUM values based om multiple criteria where one is a range (list of values)?
I am using Google Sheets, but it looks like nowadays it supports most of Excel functions as well.
I want to get SUM based on multiple criteria. I have found formulas for each criterion, but I have not found any way to combine those criteria.
SUM of all values (G) where E="X"
- SUMIFS(G3:G9;E3:E9;"X")
SUM of all values (G) where F is in B
- SUMPRODUCT(SUMIF(F3:F9;B3:B7;G3:G9))
What formula can I use?
SUM of all values (G) where F is in B AND E="X"
- ??
Try this:
=sumproduct(isnumber(match(F3:F9,B3:B7,0)),(E3:E9="X"),G3:G9)
A brief explanation
isnumber(match(F3:F9,B3:B7,0)) ceates a column of true (1s) and false (0s) values if the value in column F is found in column B.
(E3:E9="X") does the same thing if column E has the value X
sumproduct(): and where both the above are true (1s), these get multiplied by the corresponding value in G and the whole column is added up. If either is zero, the corresponding value is zero, of course.
- rintrojs only shows first dialog in Safari
- Automatically read a column of lowercases True and False as logical
- How do I add counts to a stacked bar graph?
- Counting the number of rows between each pair of months?
- How to create a conditional panel using a reactive object that is passed from another module?
- Plot multiple normalized stock charts from different dates into a single plot
- Select columns based on string match - dplyr::select
- Looking for a more efficient way to replace matrix elements
- modelsummary modelplot: change linewidth
- custom R function with group argument does not work while using the filter
- ggsurvplot function, risk table alignment problem
- plot running average in ggplot2
- Calling variable in df within function
- How to find position of running minimum (runMin) in a vector in R?
- Using httr2::last_response() in conjunction with purrr::possibly()
- Cropping a raster using terra does not return the expected extent
- Can janitor::clean_names be used on only certain columns in a data frame?
- Efficient way of row binding time series in a data.table, with correctly sorted timestamps
- Defining optional arguments in R when more complex function
- Can I change the cursor in plotly only when hovering over points?
- Issue Loading RStoolbox: "Cannot find proj.db" Error
- Filter CSV files for specific value before importing
- Matching the same lines from 2 different files and 2 columns
- Conditional coloring and outer borders in pdf KableExtra table in R
- How can I use observe
- Posterior predictive check for GAM (mgcv in R)
- Embed images in plotly tick labels
- Product of two beta distributions
- R: how to include a character between two repeted items in R?
- Categorizing data from 7 columns into 2