Embedded Files
RAnge-based CONditional functions
RAnge-based CONditional functions
Count
Count
Counting Cells by Condition
Counting Cells by Condition
=COUNTIF(T$3:T$348,S350)
Count all the cells between T3 and T348 that match the value in S350. The $ signs enable you to drag this formula down so that the next row counts all the cells between T3 and T348 that match the value in S351.
Counting Cells by multiple Conditions
Counting Cells by multiple Conditions
=COUNTIFS(V9:V554,"=V",$DI$9:$DI$554,"=6")
Count all the cells in a subset of column V where the value is "V" and the value for the corresponding row in column DI is "6".
Average
Average
AVERAGEIF
AVERAGEIFS
Maximum/Minimum
Maximum/Minimum
MAXIFS
MINIFS
SUMIF
SUMIFS
SUMPRODUCT
Summing Cells by Condition
Summing Cells by Condition
=SUMIF($A$3:$A$351,$Y355,V$3:V$351)
SUM all values in cells between V3 and V351 where the value in the corresponding (same row) cell in column A matches the value in Y355.
Use of "$" means that lookup will always take place against A3:A351, summed rows will be in the range 3:351 but the column summed will change as the formula is moved to cells left or right and the lookup key will change as the formula is moved to cells above or below.
Use of "$" means that lookup will always take place against A3:A351, summed rows will be in the range 3:351 but the column summed will change as the formula is moved to cells left or right and the lookup key will change as the formula is moved to cells above or below.
Sum for each row based on whether the header is F (forecast) or A (actual)....
We can use this formula in B6, drag it in to C6 then drag down to fill range B6:C15...
=SUMPRODUCT(($D6:$Q6)*($D$5:$Q$5=B$16))
We are using the key (F or A) held in B16 and B17Bibliography
Bibliography
https://exceljet.net/excels-racon-functionshttps://stackoverflow.com/questions/30842607/excel-formula-like-and-if-wildcards
COUNTIFhttps://support.microsoft.com/en-gb/office/countif-function-e0de10c6-f885-4e71-abb4-1f464816df34https://www.statology.org/count-frequency-of-text-in-excel/
COUNTIFShttps://exceljet.net/excel-functions/excel-countifs-function
MAXIFhttps://www.automateexcel.com/formulas/max-if-with-condition/
MAXIFShttps://www.automateexcel.com/formulas/max-if-with-condition/https://support.microsoft.com/en-gb/office/maxifs-function-dfd611e6-da2c-488a-919b-9b6376b28883
SUMIFhttps://exceljet.net/excel-functions/excel-sumif-function
SUMIFShttps://www.exceldemy.com/sumifs-sum-range-multiple-columns/
SUMPRODUCThttps://www.exceltip.com/summing/how-to-sum-by-matching-row-and-column.htmlhttps://www.exceltip.com/excel-functions/how-to-use-the-sumproduct-function-in-excel.html
COUNTIFhttps://support.microsoft.com/en-gb/office/countif-function-e0de10c6-f885-4e71-abb4-1f464816df34https://www.statology.org/count-frequency-of-text-in-excel/
COUNTIFShttps://exceljet.net/excel-functions/excel-countifs-function
MAXIFhttps://www.automateexcel.com/formulas/max-if-with-condition/
MAXIFShttps://www.automateexcel.com/formulas/max-if-with-condition/https://support.microsoft.com/en-gb/office/maxifs-function-dfd611e6-da2c-488a-919b-9b6376b28883
SUMIFhttps://exceljet.net/excel-functions/excel-sumif-function
SUMIFShttps://www.exceldemy.com/sumifs-sum-range-multiple-columns/
SUMPRODUCThttps://www.exceltip.com/summing/how-to-sum-by-matching-row-and-column.htmlhttps://www.exceltip.com/excel-functions/how-to-use-the-sumproduct-function-in-excel.html
Page updated
Google Sites
Report abuse