Help with Countif Across Multiple Columns if the Row Starts with "Project Mangement"

Michael T.

Hello,

I am struggling with a formula and hope the community can assist me.

I've created a helper sheet to automatically pull in the month, day, and day of the week. The goal is to count the total number of green or blue symbols that occur in August if that row starts with "Project Management". For example, looking at the attached screenshot, the number for August would be 12.

I have tried multiple countifs formulas but continue to get #unparseable or #incorrect argument results.

I appreciate any help that you can provide.

Michael

Paul Newcome

@brianschmidt Your COUNTIFS should end up throwing an error because you have two different range sizes within the same function. Typically the "Incorrect Argument Set" error would happen.

@Michael T. Using the vertical format as in your latest screenshot, you would use a COUNTIFS along the lines of:

=COUNTIFS([Role 1]:[Role 1], OR(@cell = "Green", @cell = "Blue"), Date:Date, varies depending on your exact needs)

The date criteria can vary depending on your exact needs. You can specify between two dates, within a certain month/year combo, or within a certain year in general. Each of the three would have different syntax.

AND(@cell >= DATE(yyyy, mm, dd), @cell <= DATE(yyyy, mm, dd))

AND(IFERROR(MONTH(@cell), 0) = ##, IFERROR(YEAR(@cell), 0) = ####)

IFERROR(YEAR(@cell), 0) = ####

Paul Newcome

Your helper columns will need to also have the month in them.

brianschmidt

Michael,

I think this is doable, but it depends on your long-term use case. Essentially, you need someway to know which columns are which month in a way that can be referenced on a row or column level. Here's how you would count for Green and Blue values on lines with departments starting with "Project Management". Note that I've assumed the column names of each day. I don't know that for sure.

=COUNTIFS([Column1]:[Column365], OR(@cell="Green", @cell="Blue"), Department:Department, LEFT(@cell, 18 ) = "Project Management")

However, being able to tell which columns belong to which month is trickier. If your sheet was laid out for a whole year where each column represented a predictable day and month of the year, you could base your formula on that.

Not know the full extent and purpose of this sheet (and what it feeds), I do think that it may be easier to lay out your dates in one column (where your department column is now) and have your departments as separate columns. I would make the dates a date column and each row a date value (i.e. 8/20/2024, 8/21/2024, etc.). Seeing that you have multiple lines with the same department name now, you could create text columns for each of the statuses for each department (i.e. columns named "Project Management: Red", "Project Management: Yellow", etc.). Then, wherever you're pulling in the symbol data from can create counts of each symbol for that day using a SUM(COLLECT() type of formula.

I know that's a bit convoluted of an answer. If you want to provide more context for your sheet here, that may help.

Hope this helps!

Michael T.

Hello Brian,

Thank you for your response. This sheet tracks the presence of resources working on a project. The R/Y/G/B symbols represent what the resources are doing on a particular day. I have a separate sheet that calculates each department's costs (different departments have different hourly costs) and that department's travel expenses, i.e., travel days have an airfare expense + per diem + 1/2 daily car rental w/gas. Onsite days have hotel expenses, per diem, etc.

I have updated my sheet with your recommendations. Any thoughts on how I could aggregate the data based on the update sheet?

Thank you!

Paul Newcome

@brianschmidt Your COUNTIFS should end up throwing an error because you have two different range sizes within the same function. Typically the "Incorrect Argument Set" error would happen.

@Michael T. Using the vertical format as in your latest screenshot, you would use a COUNTIFS along the lines of:

=COUNTIFS([Role 1]:[Role 1], OR(@cell = "Green", @cell = "Blue"), Date:Date, varies depending on your exact needs)

The date criteria can vary depending on your exact needs. You can specify between two dates, within a certain month/year combo, or within a certain year in general. Each of the three would have different syntax.

AND(@cell >= DATE(yyyy, mm, dd), @cell <= DATE(yyyy, mm, dd))

AND(IFERROR(MONTH(@cell), 0) = ##, IFERROR(YEAR(@cell), 0) = ####)

IFERROR(YEAR(@cell), 0) = ####

Michael T.

@Paul Newcome Thank you for the reply. I used your formula but needed to make a small change to the section on the date as it was returning a 0. I switched the >= with the <= to count the number of "green" and blue" symbols between the dates.

=COUNTIFS([Role 1]:[Role 1], OR(@cell = "green", @cell = "blue"), Date:Date, AND(@cell <= DATE (2024, 8, 31), @cell >= DATE(2024, 8, 1)))

Using your formula, I should have just switched the dates around :)

Is it possible to run this same formula across all "Role" columns and only count the "green" and "blue" symbols where the Department is listed as "PM" and the dates fall within a month?

I tried using your formula by adding a new "Department" range but kept getting an error.

Thank you.

Paul Newcome

You would have to do separate COUNTIFS for each role column and then add them together.

Michael T.

I simplified further, using the MONTH function so I can search by Month and not between two dates. If I want to count the number of green or blue symbols across five columns, if the Month is in January, why won't it provide the full count? Due to issues with range sizes, I created four blank columns beside the Month to help search the date. When running the below formula, it only counts the green cells in the PM1 column and not those in all five.

=COUNTIFS([PM 1]:[PM 5], "Green", Month:[Blank 3], AND(@cell = "Jan"))

Paul Newcome

Your helper columns will need to also have the month in them.