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>
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
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.
 
Upvote 0
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 (*)?
 
Upvote 0
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.
 
Upvote 0
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>
 
Upvote 0
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.
 
Upvote 0
Hi Marcelo
Many thanks for that suggestion. I will check it out in the morning. FYI closed orders are excluded (Sheet1!$F:$F,"")
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,521
Messages
6,114,109
Members
448,548
Latest member
harryls

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