Average & Sum using Count If excluding Blank Cells

R

ravi2628

Board Regular
Joined
Dec 20, 2017
Messages
230
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Dear Friends,

I was using a Formula where i need to exclude blank cells at average or sum in the given range criteria.

Due to IT Restriction Macro or addons can not work out to get the raw data

Due to Current formula count i was getting is 464
Current Formula :=ROUNDUP(AVERAGE(COUNTIFS('20-Jan-25 to 03-Mar-25'!$AD:$AD,E$25:G$29,'20-Jan-25 to 03-Mar-25'!$D:$D,Summary!$A8)),0)

Between e25:G29 empty cells has to be excluded while calculation

But actual Count is 696

Please help me out with the formula

Snap shot for ref:
1741115184865.png
 
Sort by date Sort by votes
since you are using O365, you have the option of the GroupBy Function to sum or average your data. Additionally, you can do the same with Power Query (Get and Transform Data) which is found on the Data Tab of the Ribbon. There is a GroupBy function within Power query to allow the same. We cannot manipulate data in a picture If you wish examples, then repost your sample using XL2BB so that we dont have to recreate your data to help you.
 
Upvote 0
alansidman said:
since you are using O365, you have the option of the GroupBy Function to sum or average your data. Additionally, you can do the same with Power Query (Get and Transform Data) which is found on the Data Tab of the Ribbon. There is a GroupBy function within Power query to allow the same. We cannot manipulate data in a picture If you wish examples, then repost your sample using XL2BB so that we dont have to recreate your data to help you.
Click to expand...
Please find the sample data

Current Formula Count is 9

Actual Count is 18

1741120917460.png




Productivity%Day
130.30%20-01-2025
116.32%20-01-2025
115.47%20-01-2025
94.84%20-01-2025
105.01%20-01-2025
134.89%20-01-2025
101.07%20-01-2025
107.33%20-01-2025
143.88%20-01-2025
100.70%20-01-2025
118.14%20-01-2025
107.89%20-01-2025
116.18%20-01-2025
87.01%20-01-2025
106.23%20-01-2025
127.86%20-01-2025
114.91%20-01-2025
76.92%20-01-2025
102.48%21-01-2025
76.27%21-01-2025
163.15%21-01-2025
103.18%21-01-2025
101.78%21-01-2025
120.55%21-01-2025
102.89%21-01-2025
103.96%21-01-2025
122.04%21-01-2025
117.44%22-01-2025
85.37%22-01-2025
119.05%22-01-2025
87.58%22-01-2025
92.39%22-01-2025
118.69%22-01-2025
94.78%22-01-2025
124.06%23-01-2025
86.70%23-01-2025
146.49%23-01-2025
98.69%23-01-2025
79.03%23-01-2025
119.89%23-01-2025
96.59%23-01-2025
105.10%23-01-2025
133.78%23-01-2025
105.74%23-01-2025
91.67%23-01-2025
109.48%23-01-2025
85.86%23-01-2025
103.63%23-01-2025
127.36%23-01-2025
93.91%23-01-2025
90.22%23-01-2025
126.35%24-01-2025
81.59%24-01-2025
133.25%24-01-2025
100.37%24-01-2025
97.00%24-01-2025
125.64%24-01-2025
95.08%24-01-2025
100.17%24-01-2025
124.39%24-01-2025
112.83%24-01-2025
85.13%24-01-2025
97.38%24-01-2025
90.06%24-01-2025
101.26%24-01-2025
120.26%24-01-2025
105.06%24-01-2025
73.75%24-01-2025
 
Upvote 0
I think I have muddled through your ask

Power query
Power Query: 
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Grouped Rows" = Table.Group(Source, {"Day"}, {{"Count", each Table.RowCount(_), Int64.Type}})
in
    #"Grouped Rows"

GroupBy Function
Excel Formula: 
=GROUPBY(Table1[[#All],[Day]],Table1[[#All],[Day]],COUNTA,3)
Group.xlsx
DEFGH
1Power QueryGroup By
2DayCountDayDay
31/20/2025181/20/202518
41/21/202591/21/20259
51/22/202571/22/20257
61/23/2025171/23/202517
71/24/2025171/24/202517
8Total68
Sheet1
Cell Formulas
RangeFormula
G2:H8G2=GROUPBY(Table1[[#All],[Day]],Table1[[#All],[Day]],COUNTA,3)
Dynamic array formulas.
 
Upvote 0
Try the following.
Please check the results.

T202503a.xlsm
DEF
1
2 COUNTAVERAGE
320-Jan-2518111.39%
421-Jan-259110.70%
522-Jan-257102.19%
623-Jan-2517105.78%
724-Jan-2517104.09%
8Total68107.12%
9
1e
Cell Formulas
RangeFormula
D2:F8D2=GROUPBY(Table1[[#All],[Day]],Table1[[#All],[Productivity%]],HSTACK(COUNT,AVERAGE),1,1,)
Dynamic array formulas.
 
Upvote 0
Dave Patton said:
Try the following.
Please check the results.

T202503a.xlsm
DEF
1
2 COUNTAVERAGE
320-Jan-2518111.39%
421-Jan-259110.70%
522-Jan-257102.19%
623-Jan-2517105.78%
724-Jan-2517104.09%
8Total68107.12%
9
1e
Cell Formulas
RangeFormula
D2:F8D2=GROUPBY(Table1[[#All],[Day]],Table1[[#All],[Productivity%]],HSTACK(COUNT,AVERAGE),1,1,)
Dynamic array formulas.
Click to expand...
i was looking for average of day count not the percentage
 
Upvote 0
Dave Patton said:
What results do you expect?
Click to expand...
i was looking to get the average count of random dates in a range where i have empty cells in between the range

Productivity%_________DaysFiltered Date(Column)Output(Average Count of Days)
130.30%20-01-202520-01-2025Current Formula out is 9
116.32%20-01-2025=ROUNDUP(AVERAGE(COUNTIFS( Days Column, Filtered date Column)),0)
115.47%20-01-2025
94.84%20-01-202524-01-2025
105.01%20-01-2025Required Out is 18(where i can exclude Blanks in Filter Column)
134.89%20-01-2025
101.07%20-01-2025
107.33%20-01-2025
143.88%20-01-2025
100.70%20-01-2025
118.14%20-01-2025
107.89%20-01-2025
116.18%20-01-2025
87.01%20-01-2025
106.23%20-01-2025
127.86%20-01-2025
114.91%20-01-2025
76.92%20-01-2025
102.48%21-01-2025
76.27%21-01-2025
163.15%21-01-2025
103.18%21-01-2025
101.78%21-01-2025
120.55%21-01-2025
102.89%21-01-2025
103.96%21-01-2025
122.04%21-01-2025
117.44%22-01-2025
85.37%22-01-2025
119.05%22-01-2025
87.58%22-01-2025
92.39%22-01-2025
118.69%22-01-2025
94.78%22-01-2025
124.06%23-01-2025
86.70%23-01-2025
146.49%23-01-2025
98.69%23-01-2025
79.03%23-01-2025
119.89%23-01-2025
96.59%23-01-2025
105.10%23-01-2025
133.78%23-01-2025
105.74%23-01-2025
91.67%23-01-2025
109.48%23-01-2025
85.86%23-01-2025
103.63%23-01-2025
127.36%23-01-2025
93.91%23-01-2025
90.22%23-01-2025
126.35%24-01-2025
81.59%24-01-2025
133.25%24-01-2025
100.37%24-01-2025
97.00%24-01-2025
125.64%24-01-2025
95.08%24-01-2025
100.17%24-01-2025
124.39%24-01-2025
112.83%24-01-2025
85.13%24-01-2025
97.38%24-01-2025
90.06%24-01-2025
101.26%24-01-2025
120.26%24-01-2025
105.06%24-01-2025
73.75%24-01-2025
 
Upvote 0
This M code will get you the average count of dates if I understand correctly what you want.

Power Query: 
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Grouped Rows" = Table.Group(Source, {"Day"}, {{"Count", each Table.RowCount(_), Int64.Type}}),
    #"Calculated Average" = List.Average(#"Grouped Rows"[Count]),
    #"Converted to Table" = #table(1, {{#"Calculated Average"}})
in
    #"Converted to Table"


This results in 13.6

Alternatively, take the GroupBy total shown in post #4 of 68 and divide it by CountA(Range of Counts). This also results in 13.6
 
Last edited:
Upvote 0
alansidman said:
This M code will get you the average count of dates if I understand correctly what you want.

Power Query: 
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Grouped Rows" = Table.Group(Source, {"Day"}, {{"Count", each Table.RowCount(_), Int64.Type}}),
    #"Calculated Average" = List.Average(#"Grouped Rows"[Count]),
    #"Converted to Table" = #table(1, {{#"Calculated Average"}})
in
    #"Converted to Table"


This results in 13.6

Alternatively, take the GroupBy total shown in post #4 of 68 and divide it by CountA(Range of Counts). This also results in 13.6
Click to expand...
Hi Alansidman,

as i have said i want average count of dates with in the array range where array has some blanks

Formula
average(countif(A:A,{20-Jan-2025,24-Jan-2025})) then my output is 18 where as i was able to mention dates in side the { } range if they are less in number, but now i have a long set of array where it contains from D2:D200 where in between some blank cells are available due to this the average count is calculating the blank cells as well.

Average(Countif(A:A,D2:200)) then output is different because average is calculating the blank cells data as 0
 
Upvote 0
You must log in or register to reply here.

Similar threads

N
  • Question Question
Rolling average of the last 4 populated cells
Replies
6
Views
308
Fluff
Fluff
D
  • Question Question
Average across multiple worksheets excluding unfilled cells
Replies
4
Views
378
Domenic
D
D
  • Question Question
Possible Use of CountIF and AVERAGE Functions
Replies
1
Views
300
Denny57
D
D
  • Solved
Counting Consecutive Blanks
Replies
9
Views
911
felixstraube
F
V
  • Solved
Formula to calculate automatically progressive average
Replies
5
Views
587
etaf
etaf

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top