What formula to use to count all instances of a value across multiple columns.

SteCoxy

Hello,

My colleagues have set up a sheet to help them track sustainability actions in their department. They've asked me to help with setting up reports and a dashboard that summarise and calculates the types of actions across the 4 Action type columns, as well as summarising further by team.

As you can see there's been an entry in the sheet already, but neither Materials & Waste or Energy is registering with the formulas I've used:

=COUNTIFS([Type of Action #1]:[Type of Action #1], "Materials & Waste", [Type of Action #2]:[Type of Action #2], "Materials & Waste", [Type of Action #3]:[Type of Action #3], "Materials & Waste", [Type of Action #4]:[Type of Action #4], "Materials & Waste")

=COUNTIFS([Type of Action #1]:[Type of Action #1], "Energy", [Type of Action #2]:[Type of Action #2], "Energy", [Type of Action #3]:[Type of Action #3], "Energy", [Type of Action #4]:[Type of Action #4], "Energy")

I think what it's doing is calculating only when all 4 columns has these values in, not counting up when it may be in only 1 or more columns, so I think it needs an OR function in there somewhere, but I'm stumped how to do this.

Any advice would be much appreciated!

Dale Murphy

@SteCoxy You are right that COUNTIFS is an AND function:

I would use COUNTIF and span the columns of interest:

=COUNTIF([Type of Action #1]:[Type of Action #4], ="Materials & Waste")

should give you the result if there is that specific data in any of those columns.

I don't see how you are collecting the information about which team is reporting.

In general, how do you plan to collect the data? Have you built a form for that purpose?

dm

SteCoxy

Hi Dale,

Thank you for your swift response! That seems to have worked on the "easier" calculation where I'm asking for it to calculate ALL instances of the action values across the columns.

I also then need it to calculate all instances of the actions based upon team area, for example:

=COUNTIFS([Your Area]:[Your Area], "News Content", [Type of Action #1]:[Type of Action #1], "Materials & Waste", [Type of Action #2]:[Type of Action #2], "Materials & Waste", [Type of Action #3]:[Type of Action #3], "Materials & Waste", [Type of Action #4]:[Type of Action #4], "Materials & Waste")

Using your advice above, I tried to simplify it to:

=COUNTIFS([Your Area]:[Your Area], "News Content", [Type of Action #1]:[Type of Action #4], "Materials & Waste")

but it's coming up with the error: INCORRECT ARGUMENT SET.

For reference, the information is being collated through a form, which feeds into the sheet for the team reviewing the information to analyse. The submitters of the data don't have access to the sheet.

SteCoxy

For anyone facing a similar scenario, what I ended up doing was scrapping the sheet summary method and instead created a metrics sheet.

I think the issue stemmed from the fact the columns in question in the sheet were not immediately next to each other, so it makes a formula more difficult to configure.

Instead, using the metrics sheet, the formula ended up comprising of 4 separate COUNTIFS formulas that were added together:

=COUNTIFS({Action Range 1}, [Action Type]@row, {Area Range}, "News Content") + (COUNTIFS({Action Range 2}, [Action Type]@row, {Area Range}, "News Content") + (COUNTIFS({Action Range 3}, [Action Type]@row, {Area Range}, "News Content") + (COUNTIFS({Action Range 4}, [Action Type]@row, {Area Range}, "News Content"))))