countif column values based on a monthly value

Hi,

I am creating a table of count of values in a column based on what month is reported in another. the table I want is attached. The formula I am using is as below. Can anyone advise what need to change in the planned formula

=SUMIF{V2.0 Eirabot - Gantt Range 1}},"12", {V2.0 Eirabot - Gantt Range 1}

I also want to have an actual row beneath. I have found issues where the two different calcs are providing the same values. The fact that smartsheets demotes a random range name rather than a pre-defined column name makes it difficult to reconsiliate. Any advice?

regards

Alan

Find more posts tagged with

Sheets

Formulas

Reports

Comments

Paul Newcome

If all you need is a count then try something like this...

=COUNTIFS({V2.0 Eirabot - Gantt Range 1}, MONTH(@cell) = 12)

In regards to your formula posted:

=SUMIF{V2.0 Eirabot - Gantt Range 1}},"12", {V2.0 Eirabot - Gantt Range 1}

You are missing an opening parenthesis, you have an additional curly bracket after the first cross sheet reference, you are not specifying that the MONTH needs to be 12 so the formula is looking for the EXACT text of "12", the second cross sheet reference being the same as the first means that you are trying to sum Range 1 which only needs to be specified if different from the criteria range (won't break anything by repeating it as long as proper data is in there, just means a few extra unnecessary steps), and there is no closing parenthesis.

To sum based on dates, you would need something like this...

=SUMIFS({Cross Sheet Reference Range to be Summed}, {Cross Sheet Reference Range containing Dates}, MONTH(@cell) = 12)

ALAN.ROSSNEY

thanks @paul

I tried the following for sumif

=SUMIFS({V2.0 Eirabot - Effort}, {V2.0 Eirabot - date}, MONTH(@cell) = 12)

does this look correct?

Paul Newcome

Yes. That would be correct if you are trying to add values together based on the month in the date column being that of December.

ALAN.ROSSNEY

Hi Paul,

thanks for the continued support. I got the table of FTE's per month working with the following formula

=[SUMIF([V2.0 Eirabot - month1],12,{V2.0 Eirabot - Effort})]/160

Paul Newcome

Excellent. Happy to help.

Sign in to join the conversation!

Quick Links

Trending discussions

Add Package Count Based on Customer
I have a sheet with a list of customers in one column, and then the following columns are City, Monday, Tuesday, Wednesday, Thursday, Friday. I need help with a formula that I can put in my sheet summary so that if the customer column says Staples (It can say this in multiple rows) that it will tell me the total package…
Countifs result not matching filter
In my sheet, I have a filter for 2 values (see below images). The result is 294 In my report this formula yields 304. =COUNTIFS({helper-child}, "1", {gapStatus}, <>"Rejected (not a GAP)", {gapStatus}, <>"removed - duplicate", {gapStatus}, <>"removed - not valid") Why are they not matching? What am I missing?
Using CONTAINS in a MAX(COLLECT) function
I have a schedule that has a task name column, a date column and a task type column. I am trying to build a formula (in another sheet) that will return the latest date based on when the task type is "APP" and the task name contains "GS" somewhere in the cell. Here is the formula I have come up with: MAX(COLLECT({Schedule…