# Countifs with logic in another column for Dupilcates

#### m09151014

##### New Member
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.

### Excel Facts

Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

#### AlphaFrog

##### MrExcel MVP
Try this...
=COUNTIFS(\$E:\$E,">="&\$B19, \$G:\$G,"<="&\$B19)

Last edited:

#### gaz_chops

##### Well-known Member
Maybe

=COUNTIFS(\$F:\$F,\$C\$19,\$J:\$J,"<>"&1)+COUNTIFS(\$H:\$H,\$C\$19,\$J:\$J,"<>"&1)

#### m09151014

##### New Member
Try this...
=COUNTIFS(\$E:\$E,">="&\$B19, \$G:\$G,"<="&\$B19)
Thanks but this gave me an answer of 1
=COUNTIFS(\$E:\$E,">="&\$B19,\$G:\$G,"<="&B19)

#### Eric W

##### MrExcel MVP

Consider:

Book3
BCDEFGHI
5DateDayDateDayClosed Days
67/26/2020Sunday7/27/2020Monday2
77/27/2020Monday7/27/2020Monday1
87/26/2020Sunday8/1/2020Saturday7
97/26/2020Sunday8/1/2020Saturday7
107/26/2020Sunday7/26/2020Sunday1
117/28/2020Tuesday8/1/2020Saturday5
127/30/2020Thursday7/30/2020Thursday1
137/27/2020Monday7/27/2020Monday1
147/26/2020Sunday7/28/2020Tuesday3
157/26/2020Sunday7/27/2020Monday2
167/30/2020Thursday7/30/2020Thursday1
17
18DayStore Count# of stores closed on this day
197/26/2020Sunday66
207/27/2020Monday47
217/28/2020Tuesday24
227/29/2020Wednesday03
237/30/2020Thursday25
247/31/2020Friday03
258/1/2020Saturday33
Sheet7
Cell Formulas
RangeFormula
I6:I16I6=G6-E6+1
D19:D25D19=SUMPRODUCT(--(MMULT(--(\$F\$6:\$H\$16=C19),{1;1;1})>0))
F19:F25F19=COUNTIFS(\$E\$6:\$E\$16,"<="&B19,\$G\$6:\$G\$16,">="&B19)

If you're trying to see how many times "Sunday" occurs in either column F or H, but only count it once if it's in both, then try the D19 formula. If you want to see how many stores are closed on the given day (7/26/2020), try the F19 formula.

#### m09151014

##### New Member
Maybe

=COUNTIFS(\$F:\$F,\$C\$19,\$J:\$J,"<>"&1)+COUNTIFS(\$H:\$H,\$C\$19,\$J:\$J,"<>"&1)
Thanks but, this gave me 7 it didn't subtract duplicate on row 10.

#### m09151014

##### New Member

Consider:

Book3
BCDEFGHI
5DateDayDateDayClosed Days
67/26/2020Sunday7/27/2020Monday2
77/27/2020Monday7/27/2020Monday1
87/26/2020Sunday8/1/2020Saturday7
97/26/2020Sunday8/1/2020Saturday7
107/26/2020Sunday7/26/2020Sunday1
117/28/2020Tuesday8/1/2020Saturday5
127/30/2020Thursday7/30/2020Thursday1
137/27/2020Monday7/27/2020Monday1
147/26/2020Sunday7/28/2020Tuesday3
157/26/2020Sunday7/27/2020Monday2
167/30/2020Thursday7/30/2020Thursday1
17
18DayStore Count# of stores closed on this day
197/26/2020Sunday66
207/27/2020Monday47
217/28/2020Tuesday24
227/29/2020Wednesday03
237/30/2020Thursday25
247/31/2020Friday03
258/1/2020Saturday33
Sheet7
Cell Formulas
RangeFormula
I6:I16I6=G6-E6+1
D19:D25D19=SUMPRODUCT(--(MMULT(--(\$F\$6:\$H\$16=C19),{1;1;1})>0))
F19:F25F19=COUNTIFS(\$E\$6:\$E\$16,"<="&B19,\$G\$6:\$G\$16,">="&B19)

If you're trying to see how many times "Sunday" occurs in either column F or H, but only count it once if it's in both, then try the D19 formula. If you want to see how many stores are closed on the given day (7/26/2020), try the F19 formula.
THANKS so much the D19 formula works!!!

#### AlphaFrog

##### MrExcel MVP
Thanks but this gave me an answer of 1
=COUNTIFS(\$E:\$E,">="&\$B19,\$G:\$G,"<="&B19)

Sorry. Had the operators backwards. Try this...

=COUNTIFS(\$E:\$E,"<="&\$B19,\$G:\$G,">="&\$B19)

Replies
7
Views
160
Replies
9
Views
86
Replies
7
Views
103
Replies
3
Views
173
Replies
8
Views
118

1,132,910
Messages
5,655,902
Members
418,250
Latest member
Jebacmakro

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back