Sumifs function in conjunction with other formula, counta, countif and countifs

[leenux_tux]

Hello Forum,

Trying to solve a problem in Excel and cannot find the combination of formulas to fix. Been racking my brain and "googling" but to no avail. Wondering if anyone else on the forums here has had a similar challenge.

Some background first.

• There are multiple records keyed to the month, i.e. October which is column A
• There are no fixed number of records for the month, could be 100, could be 150, could be 200.
• There are multiple columns on sheet with data I want to do calculations on based on the value that is in column A.
• The calculations are not simple "sum", could be working out average so need to use "counta" and/or countif/countifs with the sum

I have looked into using sumifs, keying to the month in column A, however, I haven't seen any examples where the calculation being done isn't a standard "sum" (i.e. a divide, or multiply)

Some sample data below. Example calculation, I only want to an average of data in column C where Column A is October.


[TABLE="width: 500"]
<tbody>[TR]
[TD]SEPTEMBER[/TD]
[TD]16.00[/TD]
[TD]£32.00[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]SEPTEMBER[/TD]
[TD]-24.00[/TD]
[TD]-£12.00[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]OCTOBER[/TD]
[TD]-28.60[/TD]
[TD]-£28.60[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]OCTOBER[/TD]
[TD]2.50[/TD]
[TD]£5.00[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]OCTOBER[/TD]
[TD]-15.90[/TD]
[TD]-£15.90[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

To me you would use sumifs to say

"Only use values where column A is OCTOBER"

Then
"Count the number of instances of OCTOBER" and use that value in the divide for the sum of (for example) column C

Hopefully I have given a reasonable description of the problem.

Thanks in advance

L

 

[Fluff]

Have a look at Averageif or AverageIfs
https://www.ablebits.com/office-addins-blog/2015/08/28/excel-average-averageif/

 

[leenux_tux]

Fluff, thanks for the prompt reply, will take a look.