Countifs with logic in another column for Dupilcates

m09151014

New Member
Joined
Jul 18, 2008
Messages
20
Office Version
  1. 365
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.

1596645205947.png
 

Some videos you may like

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,378
Try this...
=COUNTIFS($E:$E,">="&$B19, $G:$G,"<="&$B19)
 
Last edited:

gaz_chops

Well-known Member
Joined
Apr 29, 2003
Messages
6,468
Platform
  1. MacOS
Maybe

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

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,617

ADVERTISEMENT

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
Joined
Jul 18, 2008
Messages
20
Office Version
  1. 365

ADVERTISEMENT

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!!!
 

Watch MrExcel Video

Forum statistics

Threads
1,122,518
Messages
5,596,630
Members
414,082
Latest member
sasmita

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top