Formula to count "low", "med", or "high" in 4 columns of data split by fiscal quarter

Hello,

I'm trying to work out the formula to achieve the following, but I can't get it to work.

I have 5 columns. The first is populated with fiscal quarter (q1, q2, q3, q4) based on dates in another column. The other columns represent 4 service areas (we'll call them Family, Lifestyle, Emotional and Social). Within each of these 4 areas I am recording whether a client need for support is low, medium or high. Not every client needs all areas of support, so there are a lot of blanks.

I need to produce a report which summarises the levels of support given quarterly under each service area. So in short I need to be able to say that 'In quarter 3, for Family, we had 10 low referrals, 15 medium referrals and 20 high referrals.'

How would I write a formula to produce this? So far I've got =COUNTIFS(LifeandAssoc, "Low", EOSMonthOutcomes,"q3"), but this returns an error so clearly I'm going wrong. EDIT: just to say that LifeandAssoc and EOSMonthOutcomes are named ranges, in case that wasn't clear.

I hope that makes sense, it's quite hard to explain.

Thanks, Kerry

Fast answers need visual help. Please read the yellow banner at the top of this page on how to attach a file.

Be sure to dummy up the confidential data. It is important to see and understand the formatting and values.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Why not save a lot of hassle and capture your data in a normalised table so that you can analyse it with a Pivot Table.

See SHeet 2 in the attached.

Being a Pivot Table of course you can dice and slice it just by dragging field labels around in the Pivot Table Fields window.

Originally Posted by Kerry1980

I tried. I'm using pivot tables for a lot of the other reporting, but it makes it overly complex when what I need is actually pretty simple. I might have another go though and see if I can filter out some of the unnecessary bits. Thanks.

That would be simple enough. Just create a SLICER to filter the PT.