Using the COUNTIF(S) and SUMIF(S) functions with OR

[frichmond]

Hi,

Currently I am averaging values that match ANY OF 4 different benchmarks. My first solution was to do a seperate SUMIF for each benchmark and add their total. Then divide that result by the sum of 4 seperate Countif functions to arrive at the average.

I am looking for a way to use a criteria such that a data in the range must belong to any of the benchmarks using only 1 countif and 1 sumif function, instead of 4 of each.

I think the answer lies in the correct usage of the OR function to check whether a value is 1 bechmark or the other, or the other, or the other etc


Here is one such function. Here, K2,K3,K4 and K5 represent the 4 different benchmarks. A5 and B5 represet the date range the data must be in. I know that SUMIF and COUNTIF operate like AND funcaitons such that all criteria must be met, but finding a way to use OR would make this code a lot cleaner

=(SUMIFS(June!$F$2:$F$65536,June!$D$2:$D$65536,"<="&B5,June!$D$2:$D$65536,">="&A5,June!$C$2:$C$65536,$K$2)+
SUMIFS(June!$F$2:$F$65536,June!$D$2:$D$65536,"<="&B5,June!$D$2:$D$65536,">="&A5,June!$C$2:$C$65536,$K$3)+
SUMIFS(June!$F$2:$F$65536,June!$D$2:$D$65536,"<="&B5,June!$D$2:$D$65536,">="&A5,June!$C$2:$C$65536,$K$4)+
SUMIFS(June!$F$2:$F$65536,June!$D$2:$D$65536,"<="&B5,June!$D$2:$D$65536,">="&A5,June!$C$2:$C$65536,$K$5))
/
(COUNTIFS(June!$D$2:$D$65536,"<"&B5,June!$D$2:$D$65536,">="&A5,June!$C$2:$C$65536,$K$2)+
COUNTIFS(June!$D$2:$D$65536,"<"&B5,June!$D$2:$D$65536,">="&A5,June!$C$2:$C$65536,$K$3)+
COUNTIFS(June!$D$2:$D$65536,"<"&B5,June!$D$2:$D$65536,">="&A5,June!$C$2:$C$65536,$K$4)+
COUNTIFS(June!$D$2:$D$65536,"<"&B5,June!$D$2:$D$65536,">="&A5,June!$C$2:$C$65536,$K$5))

If you need more explanation or examples, please let me know. Thanks!

[Ace_XL]

Do away with the SUMIFS and COUNTIFS. Use AVERAGEIFS instead. This will shorten the formula by half

[frichmond]

If I use averageif I can only get the average of the 4 averages (1 of each benchmark) which is different than the average of all data from all 4 benchmarks.

Heres what I think you are suggesting.

=AVERAGE(AVERAGEIFS(June!$F$2:$F$65536,June!$D$2:$D$65536,">="&$C$31,June!$C$2:$C$65536,$K$2,June!$F$2:$F$65536,">0"),
AVERAGEIFS(June!$F$2:$F$65536,June!$D$2:$D$65536,">="&$C$31,June!$C$2:$C$65536,$K$3,June!$F$2:$F$65536,">0"),
AVERAGEIFS(June!$F$2:$F$65536,June!$D$2:$D$65536,">="&$C$31,June!$C$2:$C$65536,$K$4,June!$F$2:$F$65536,">0"),
AVERAGEIFS(June!$F$2:$F$65536,June!$D$2:$D$65536,">="&$C$31,June!$C$2:$C$65536,$K$5,June!$F$2:$F$65536,">0"))

Correct me if my thinking is wrong here, please.

[Ace_XL]

Yes you are correct, you would need to average out the sums of the averageifs to achieve your desired result