Counting Table

Steve 1962

Active Member
Joined
Jan 3, 2006
Messages
282
Office Version
  1. 365
Hi

I did repost this post but I'm not sure that it came through, so just posting again just in case it didn't work the first time. Apologies if this is a double-up.

I have added an additional column C (Exception) which has either a 1 or 2 or can be some other number.

I would like the current counting table that we now have working fine to now only count the entries if they are anything but 1 in the "Exception" column.
Eg: Count as normal but do not count those cells which have a 1 in them.

Thanks

Book1
ABCDEFGHIJK
1DateReferenceExceptionStartEndReference5C42111234678
21/04/20205C421CairoNew YorkAllowed LocationsBostonCairoBostonAuckland
31/04/20205C421BostonNew YorkNew YorkAucklandNew YorkLas Vegas
41/04/20205C421BostonNew YorkCalgaryNew YorkCairoLos Angeles
51/04/20201111DallasBoston
61/04/20205671BostonCalgary
72/04/20202341BostonCairoShould Read - 5C42111234678
82/04/20202341Los AngelesBoston1/04/20201100
92/04/20205671BostonBoston2/04/20200110
102/04/20205671BostonNew York3/04/20200203
112/04/20201111BostonNew York4/04/20200010
122/04/20205672BostonNew York
133/04/20201112New YorkNew York
143/04/20201112New YorkLos Angeles
153/04/20201112Las VegasNew York
163/04/20206782New YorkCairo
173/04/20206782New YorkCairo
183/04/20206781SeattleCairo
194/04/20201111New YorkCairo
204/04/20201112New YorkCairo
214/04/20205672New YorkBoston
224/04/20202342AucklandBoston
234/04/20202372CairoBoston
244/04/20202372AucklandBoston
Mr Excel
Cell Formulas
RangeFormula
H8:K11H8{=SUM(--(FREQUENCY(IF(ISNA(MATCH($D$2:$E$24,H$2:H$4,0))*ISNUMBER(MATCH($A$2:$A$24,$G8,0))*ISNUMBER(MATCH($B$2:$B$24,H$1,0)),ROW($A$2:$A$24)-ROW($A$2)+1,""),ROW($A$2:$A$24)-ROW($A$2)+1)>0))}
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.
 

Some videos you may like

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,558
Office Version
  1. 365
Platform
  1. Windows
I think that this is what you're asking for, in H8, remember to confirm the array with Ctrl Shift Enter

=SUM(--(FREQUENCY(IF(ISNA(MATCH($D$2:$E$24,H$2:H$4,0))*ISNUMBER(MATCH($A$2:$A$24,$G8,0))*ISNUMBER(MATCH($B$2:$B$24,H$1,0))*($C$2:$C$24<>1),ROW($A$2:$A$24)-ROW($A$2)+1,""),ROW($A$2:$A$24)-ROW($A$2)+1)>0))
 

Amit Tandon

Board Regular
Joined
May 23, 2020
Messages
84
Office Version
  1. 365
Platform
  1. Windows
In continuation of the previous / separate post by you, you may use below Array Formula (Ctrl+Shift+Enter) - this adds another condition of "($C$2:$C$24<>1)" to my earlier formula, to exclude column C cells with 1, and as already stated by jasonb:

=SUM(--(FREQUENCY(IF(ISNA(MATCH($D$2:$E$24,H$2:H$4,0))*ISNUMBER(MATCH($A$2:$A$24,$G8,0))*ISNUMBER(MATCH($B$2:$B$24,H$1,0))*($C$2:$C$24<>1),ROW($A$2:$A$24)-ROW($A$2)+1,""),ROW($A$2:$A$24)-ROW($A$2)+1)>0))
 

Steve 1962

Active Member
Joined
Jan 3, 2006
Messages
282
Office Version
  1. 365
Hey thanks very much Amit & Jasonb.

Works fine. You guys are smart.

All the best.
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,558
Office Version
  1. 365
Platform
  1. Windows
I thought that the formula looked a bit clumsy for the task it performs, it took me a while to figure out the logic in order to clean it up a bit.

Same results, no need to array confirm.

=SUMPRODUCT(($A$2:$A$24=$G8)*($B$2:$B$24=H$1)*($C$2:$C$24<>1)*((ISNA(MATCH($D$2:$D$24,H$2:H$4,0))+ISNA(MATCH($E$2:$E$24,H$2:H$4,0)))>0))
 

Watch MrExcel Video

Forum statistics

Threads
1,118,656
Messages
5,573,442
Members
412,529
Latest member
cTatch
Top