Hi,
I am need of help. I am trying to count how many times a certain day shows up in 2 columns, and this formula works in cell D19
{=IF($J:$J=1,(COUNTIFS($F:$F,$C$19)+COUNTIFS($H:$H,$C$19)),COUNTIFS($F:$F,$C$19)+COUNTIFS($H:$H,$C$19)-1)}
for that but, I need it to subtract one from the count because a few stores were only closed for 1 day but, the countifs formula is counting it 2 times one for each column (lines 10 & 16 for example). I tried to add a helper column (J) and say if this cell is 1 then subtract 1 from the countifs sum but, the way I have the formula it is subtracting 1 from the countifs sum even if there are not duplicate days in both columns and it is not looking at my helper column.
I am open to correcting whatever is wrong with my formula or deleting and doing something else that might work better.
I am need of help. I am trying to count how many times a certain day shows up in 2 columns, and this formula works in cell D19
{=IF($J:$J=1,(COUNTIFS($F:$F,$C$19)+COUNTIFS($H:$H,$C$19)),COUNTIFS($F:$F,$C$19)+COUNTIFS($H:$H,$C$19)-1)}
for that but, I need it to subtract one from the count because a few stores were only closed for 1 day but, the countifs formula is counting it 2 times one for each column (lines 10 & 16 for example). I tried to add a helper column (J) and say if this cell is 1 then subtract 1 from the countifs sum but, the way I have the formula it is subtracting 1 from the countifs sum even if there are not duplicate days in both columns and it is not looking at my helper column.
I am open to correcting whatever is wrong with my formula or deleting and doing something else that might work better.