SUMIF COUNTIF AVERAGE problem formula...

NJS1982

Board Regular
Joined
Sep 24, 2009
Messages
183
Office Version
  1. 365
Platform
  1. Windows
Hi all, hoping that somebody can help with this formula please...

I have a list of contractors, all with multiple lines for queries (for different pieces of work), with the number of days they take to respond to each query and the fiscal year that the query falls into.

So I have the following columns:

L = Contractor name
S = Number of days to respond
AD = Fiscal year (i.e. 2018/2019).

I then have a drop down elsewhere in the sheet with 'All Years' or the fiscal year (so it can be changed) and a summary table with each contractor in (listed in column B down).

The formula I have come up with is:
=IF($B$2="All Years",
SUMIF($L$21:$L$504,B11,$S$21:$S$504)/COUNTIF($L$21:$L$504,B11),
SUMIFS($L$21:$L$504,B11,$S$21:$S$504,$AD$20:$AD$504,$B$2)
/COUNTIFS($L$21:$L$504,$B$10,$AD$21:$AD$504,$B$2))

However it is throwing out a #Value ! error and I can't see why that would be? In the above example, B11 contains 'ABC'.

I am trying to say, if B2 = All Years, then look at all of the contractors listed in column L, tell me the number of times 'ABC' appears and the number of cumulative days to respond, then divide that by the number of times 'ABC' appears in the column (to get the average).

HOWEVER, if B2 = anything other than 'All Years' (i.e. a specific fiscal year period, like 2018/2019), then do the same calculation but factor in the number of instances the contractor appears in that financial year alone.
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
One thing that stands out is is looks like your SUMIFS is not structured correctly:
Code:
[B][COLOR=#ffa500]SUMIFS($L$21:$L$504,B11,$S$21:$S$504,[U]$AD$20:$AD$504,$B$2)[/U][/COLOR][/B]
I think your second argument should be a whole range, not a single cell.
See here for the proper structure: https://exceljet.net/excel-functions/excel-sumifs-function
 
Upvote 0

Forum statistics

Threads
1,213,517
Messages
6,114,089
Members
448,548
Latest member
harryls

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