Countifs conundrum assistance needed

GerryBriant

New Member
Joined
Jan 15, 2018
Messages
16
Hi, I am trying to use Countifs to count non blank cells over 2 columns but only count as 1 if adjacent cell is populated ... If that makes sense.
I've tried =COUNTIFS('Sheet1'!$a:$a,"<>",'sheet1'!$B:$B,"<>") to no avail. :(
For below the answer I'm hoping for is 4.

Column ‘A’
Column ‘B’
01/01/19


01/01/19
01/01/19



01/01/19
01/01/19

<tbody>
</tbody>
 

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,344
Try


A
B
C
D
1
Date1​
Date2​
Result​
2
01/01/2019​
4​
3
01/01/2019​
4
01/01/2019​
5
6
01/01/2019​
01/01/2019​

Formula in D2
=SUMPRODUCT(--(MMULT(--(A2:B6<>""),{1;1})>0))

Hope this helps

M.
 

GerryBriant

New Member
Joined
Jan 15, 2018
Messages
16
Marcelo
That works brilliantly but fails if I include it as part of a larger function.
:(
 

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
22,884
How about =COUNTA(A:A)+COUNTA(B:B)-2*COUNTIFS(A:A,"<>",B:B,"<>")
 
Last edited:

GerryBriant

New Member
Joined
Jan 15, 2018
Messages
16
Hi Marcelo,
as previous post I was trying to include your brilliant suggestion as part of a greater formulae but it then fails to work...
=(COUNTIFS(‘SHEET1’!$L:$L,$A18,’SHEET1’!$OK:$OK,"Yes",’SHEET1’!$PJ:$PJ,"",’SHEET1’!$QZ:$QZ,"")*SUMPRODUCT(--(MMULT(--(‘SHEET1’!$PG:$PH<>""),{1;1})>0)))
Any suggestions what I need to do to stop it multiplying (*)?
 

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,344
Hi Marcelo,
as previous post I was trying to include your brilliant suggestion as part of a greater formulae but it then fails to work...
=(COUNTIFS(‘SHEET1’!$L:$L,$A18,’SHEET1’!$OK:$OK,"Yes",’SHEET1’!$PJ:$PJ,"",’SHEET1’!$QZ:$QZ,"")*SUMPRODUCT(--(MMULT(--(‘SHEET1’!$PG:$PH<>""),{1;1})>0)))
Any suggestions what I need to do to stop it multiplying (*)?
A sample of data that represents your real case along with the expected results would be helpful.

M.
 

GerryBriant

New Member
Joined
Jan 15, 2018
Messages
16
Hi Marcelo,
Find attached below, table via word of data table
Yr End Month
Confirmed (Yes/NO)
Open
Closed
Next Stage
Closed
Mar
No

24-Sep

12-Jul
Mar
Yes
25-Sep
25-Sep


Mar
Yes

01-Oct


Mar
Yes




Mar
Yes
25-Apr
25-Apr


Mar
Yes
23-Apr
23-Apr
01-May
20-Jun
Mar
Yes
12-Jul
16-Jul


Mar
Yes

30-Jul


Mar
Yes
29-Jul
29-Jul


Mar
Yes
29-Aug




<tbody>
</tbody>

Summary table. Formulae for March open closed is:-
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]COUNTIFS(Sheet1!$A:$A,$B$6,Sheet1!$B:$B,"Yes",Sheet1!$C:$C,"<>",Sheet1!$D:$D,"<>",Sheet1!$E:$E,"",Sheet1!$F:$F,"")*(SUMPRODUCT(--(MMULT(--(Sheet1!$C:$D<>""),{1;1})>0)))

Please advise as I was hoping for the result to only be 7[/FONT]

Y/EY/E MonthOpen or Closed
Jan-19Jan
Feb-19Feb
Mar-19Mar884
Apr-19Apr
May-19May
Jun-19Jun6
Jul-19Jul2
Aug-19Aug
Sep-19Sep
Oct-19Oct
Nov-19Nov
Dec-19Dec
Totals 892

<colgroup><col><col><col></colgroup><tbody>
</tbody>
 

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,344
Shouldn't the result be 8? It seems that rows 3, 4, 6, 7, 8, 9, 10 and 11 meet the criteria.

Something like this


A
B
C
D
E
F
G
H
I
J
1
Yr End Month​
Confirmed (Yes/NO)​
Open​
Closed​
Next Stage​
Closed​
Crit1​
Crit2​
Result​
2
Mar​
No​
24-Sep​
12/jul​
Mar​
Yes​
8​
3
Mar​
Yes​
25-Sep​
25-Sep​
4
Mar​
Yes​
01-Oct​
5
Mar​
Yes​
6
Mar​
Yes​
25-Apr​
25-Apr​
7
Mar​
Yes​
23-Apr​
23-Apr​
01-May​
20/jun​
8
Mar​
Yes​
12/jul​
16/jul​
9
Mar​
Yes​
30/jul​
10
Mar​
Yes​
29/jul​
29/jul​
11
Mar​
Yes​
29-Aug​

Criteria in H2:I2

Formula in J2
=SUMPRODUCT(--(MMULT((A2:A11=H2)*(B2:B11=I2)*(C2:F11<>""),{1;1;1;1})>0))

M.
 

GerryBriant

New Member
Joined
Jan 15, 2018
Messages
16
Hi Marcelo
Many thanks for that suggestion. I will check it out in the morning. FYI closed orders are excluded (Sheet1!$F:$F,"")
 

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,344
Hi Marcelo
Many thanks for that suggestion. I will check it out in the morning. FYI closed orders are excluded (Sheet1!$F:$F,"")
New version - excludes closed orders


A
B
C
D
E
F
G
H
I
J
1
Yr End Month​
Confirmed (Yes/NO)​
Open​
Closed​
Next Stage​
Closed​
Crit1​
Crit2​
Result​
2
Mar​
No​
24-Sep​
12-jul​
Mar​
Yes​
7​
3
Mar​
Yes​
25-Sep​
25-Sep​
4
Mar​
Yes​
01-Oct​
5
Mar​
Yes​
6
Mar​
Yes​
25-Apr​
25-Apr​
7
Mar​
Yes​
23-Apr​
23-Apr​
01-May​
20-jun​
8
Mar​
Yes​
12-jul​
16-jul​
9
Mar​
Yes​
30-jul​
10
Mar​
Yes​
29-jul​
29-jul​
11
Mar​
Yes​
29-Aug​

Criteria in H2:I2

Formula in J2
=SUMPRODUCT(--(MMULT((A2:A11=H2)*(B2:B11=I2)*(F2:F11="")*(C2:E11<>""),{1;1;1})>0))

M.
 

Forum statistics

Threads
1,085,565
Messages
5,384,474
Members
401,903
Latest member
MarBer

Some videos you may like

This Week's Hot Topics

Top