Stack CountIFS within one cell

TravisClem

HI,

I'm trying to produce a table that contains projects received within a year combined, then separated into regions. Below is the formula I'm using, I can only guess that it's subtracting the 345 from the 2106 instead of only counting the USA projects within the 345.

=COUNTIF({Projects In by Date}, <=DATE([Primary Column]@row, 12, 31)) - COUNTIFS({State}, <>"CAN-Canada", {State}, <>"OC-Outside North America")

DKazatsky2

Hi @TravisClem ,

I'm not sure what you are using for the formula in the "ALL" column, but if it's the same as the first section of your posted formula, I would think you are getting the wrong results as it would count every year up to the year in question (as opposed to only the year in question).

Either way, try this for the USA column.

=COUNTIFS({Projects In by Date}, <=DATE([Primary Column]@row, 12, 31), {Projects In by Date}, >=DATE([Primary Column]@row, 1, 1), {State}, <>"CAN-Canada", {State}, <>"OC-Outside North America")

The formula I have posted ensures only 1 year is looked at - use the first portion if it does turn out your other formula is incorrect.

Hope this helps,

Dave

DKazatsky2

Hi @TravisClem ,

I'm not sure what you are using for the formula in the "ALL" column, but if it's the same as the first section of your posted formula, I would think you are getting the wrong results as it would count every year up to the year in question (as opposed to only the year in question).

Either way, try this for the USA column.

=COUNTIFS({Projects In by Date}, <=DATE([Primary Column]@row, 12, 31), {Projects In by Date}, >=DATE([Primary Column]@row, 1, 1), {State}, <>"CAN-Canada", {State}, <>"OC-Outside North America")

The formula I have posted ensures only 1 year is looked at - use the first portion if it does turn out your other formula is incorrect.

Hope this helps,

Dave

TravisClem

That solved the issue :) Thanks @DKazatsky2 , I thought I needed to start a new COUNTIF in order to then sort by region.