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
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
Maybe

=COUNTIFS($F:$F,$C$19,$J:$J,"<>"&1)+COUNTIFS($H:$H,$C$19,$J:$J,"<>"&1)
 
Upvote 0
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.
 
Upvote 0
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!!!
 
Upvote 0

Forum statistics

Threads
1,213,526
Messages
6,114,136
Members
448,551
Latest member
Sienna de Souza

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
Back
Top