Countifs conundrum assistance needed

[GerryBriant]

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]

Try


[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

A​

[/td][td="bgcolor: #DCE6F1"]

B​

[/td][td="bgcolor: #DCE6F1"]

C​

[/td][td="bgcolor: #DCE6F1"]

D​

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

[/td][td]

Date1​

[/td][td]

Date2​

[/td][td][/td][td]

Result​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

2​

[/td][td]

01/01/2019​

[/td][td][/td][td][/td][td]

4​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

3​

[/td][td][/td][td]

01/01/2019​

[/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

4​

[/td][td]

01/01/2019​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

5​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

6​

[/td][td]

01/01/2019​

[/td][td]

01/01/2019​

[/td][td][/td][td][/td][/tr]
[/table]

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

Hope this helps

M.

 

[GerryBriant]

Marcelo
That works brilliantly but fails if I include it as part of a larger function.

 

[mikerickson]

How about =COUNTA(A:A)+COUNTA(B:B)-2*COUNTIFS(A:A,"<>",B:B,"<>")

 

[GerryBriant]

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]

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]

Hi Marcelo,
Find attached below, table via word of data table
[TABLE="width: 419"]
<tbody>[TR]
[TD]Yr End Month
[/TD]
[TD]Confirmed (Yes/NO)
[/TD]
[TD]Open
[/TD]
[TD]Closed
[/TD]
[TD]Next Stage
[/TD]
[TD]Closed
[/TD]
[/TR]
[TR]
[TD]Mar
[/TD]
[TD]No
[/TD]
[TD]
[/TD]
[TD]24-Sep
[/TD]
[TD]
[/TD]
[TD]12-Jul
[/TD]
[/TR]
[TR]
[TD]Mar
[/TD]
[TD]Yes
[/TD]
[TD]25-Sep
[/TD]
[TD]25-Sep
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]Mar
[/TD]
[TD]Yes
[/TD]
[TD]
[/TD]
[TD]01-Oct
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]Mar
[/TD]
[TD]Yes
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]Mar
[/TD]
[TD]Yes
[/TD]
[TD]25-Apr
[/TD]
[TD]25-Apr
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]Mar
[/TD]
[TD]Yes
[/TD]
[TD]23-Apr
[/TD]
[TD]23-Apr
[/TD]
[TD]01-May
[/TD]
[TD]20-Jun
[/TD]
[/TR]
[TR]
[TD]Mar
[/TD]
[TD]Yes
[/TD]
[TD]12-Jul
[/TD]
[TD]16-Jul
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]Mar
[/TD]
[TD]Yes
[/TD]
[TD]
[/TD]
[TD]30-Jul
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]Mar
[/TD]
[TD]Yes
[/TD]
[TD]29-Jul
[/TD]
[TD]29-Jul
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]Mar
[/TD]
[TD]Yes
[/TD]
[TD]29-Aug
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
</tbody>[/TABLE]

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]

[TABLE="width: 268"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Y/E[/TD]
[TD]Y/E Month[/TD]
[TD]Open or Closed[/TD]
[/TR]
[TR]
[TD]Jan-19[/TD]
[TD]Jan[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Feb-19[/TD]
[TD]Feb[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Mar-19[/TD]
[TD]Mar[/TD]
[TD]884[/TD]
[/TR]
[TR]
[TD]Apr-19[/TD]
[TD]Apr[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]May-19[/TD]
[TD]May[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Jun-19[/TD]
[TD]Jun[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]Jul-19[/TD]
[TD]Jul[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]Aug-19[/TD]
[TD]Aug[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Sep-19[/TD]
[TD]Sep[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Oct-19[/TD]
[TD]Oct[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Nov-19[/TD]
[TD]Nov[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Dec-19[/TD]
[TD]Dec[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] Totals [/TD]
[TD] [/TD]
[TD]892
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Marcelo Branco]

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


[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

A​

[/td][td="bgcolor: #DCE6F1"]

B​

[/td][td="bgcolor: #DCE6F1"]

C​

[/td][td="bgcolor: #DCE6F1"]

D​

[/td][td="bgcolor: #DCE6F1"]

E​

[/td][td="bgcolor: #DCE6F1"]

F​

[/td][td="bgcolor: #DCE6F1"]

G​

[/td][td="bgcolor: #DCE6F1"]

H​

[/td][td="bgcolor: #DCE6F1"]

I​

[/td][td="bgcolor: #DCE6F1"]

J​

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

[/td][td]

Yr End Month​

[/td][td]

Confirmed (Yes/NO)​

[/td][td]

Open​

[/td][td]

Closed​

[/td][td]

Next Stage​

[/td][td]

Closed​

[/td][td][/td][td]

Crit1​

[/td][td]

Crit2​

[/td][td]

Result​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

2​

[/td][td]

Mar​

[/td][td]

No​

[/td][td][/td][td]

24-Sep​

[/td][td][/td][td]

12/jul​

[/td][td][/td][td]

Mar​

[/td][td]

Yes​

[/td][td]

8​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

3​

[/td][td]

Mar​

[/td][td]

Yes​

[/td][td]

25-Sep​

[/td][td]

25-Sep​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

4​

[/td][td]

Mar​

[/td][td]

Yes​

[/td][td][/td][td]

01-Oct​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

5​

[/td][td]

Mar​

[/td][td]

Yes​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

6​

[/td][td]

Mar​

[/td][td]

Yes​

[/td][td]

25-Apr​

[/td][td]

25-Apr​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

7​

[/td][td]

Mar​

[/td][td]

Yes​

[/td][td]

23-Apr​

[/td][td]

23-Apr​

[/td][td]

01-May​

[/td][td]

20/jun​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

8​

[/td][td]

Mar​

[/td][td]

Yes​

[/td][td]

12/jul​

[/td][td]

16/jul​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

9​

[/td][td]

Mar​

[/td][td]

Yes​

[/td][td][/td][td]

30/jul​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

10​

[/td][td]

Mar​

[/td][td]

Yes​

[/td][td]

29/jul​

[/td][td]

29/jul​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

11​

[/td][td]

Mar​

[/td][td]

Yes​

[/td][td]

29-Aug​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

Criteria in H2:I2

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

M.

 

[GerryBriant]

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]

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


[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

A​

[/td][td="bgcolor: #DCE6F1"]

B​

[/td][td="bgcolor: #DCE6F1"]

C​

[/td][td="bgcolor: #DCE6F1"]

D​

[/td][td="bgcolor: #DCE6F1"]

E​

[/td][td="bgcolor: #DCE6F1"]

F​

[/td][td="bgcolor: #DCE6F1"]

G​

[/td][td="bgcolor: #DCE6F1"]

H​

[/td][td="bgcolor: #DCE6F1"]

I​

[/td][td="bgcolor: #DCE6F1"]

J​

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

[/td][td]

Yr End Month​

[/td][td]

Confirmed (Yes/NO)​

[/td][td]

Open​

[/td][td]

Closed​

[/td][td]

Next Stage​

[/td][td]

Closed​

[/td][td][/td][td]

Crit1​

[/td][td]

Crit2​

[/td][td]

Result​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

2​

[/td][td]

Mar​

[/td][td]

No​

[/td][td][/td][td]

24-Sep​

[/td][td][/td][td]

12-jul​

[/td][td][/td][td]

Mar​

[/td][td]

Yes​

[/td][td]

7​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

3​

[/td][td]

Mar​

[/td][td]

Yes​

[/td][td]

25-Sep​

[/td][td]

25-Sep​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

4​

[/td][td]

Mar​

[/td][td]

Yes​

[/td][td][/td][td]

01-Oct​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

5​

[/td][td]

Mar​

[/td][td]

Yes​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

6​

[/td][td]

Mar​

[/td][td]

Yes​

[/td][td]

25-Apr​

[/td][td]

25-Apr​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

7​

[/td][td]

Mar​

[/td][td]

Yes​

[/td][td]

23-Apr​

[/td][td]

23-Apr​

[/td][td]

01-May​

[/td][td]

20-jun​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

8​

[/td][td]

Mar​

[/td][td]

Yes​

[/td][td]

12-jul​

[/td][td]

16-jul​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

9​

[/td][td]

Mar​

[/td][td]

Yes​

[/td][td][/td][td]

30-jul​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

10​

[/td][td]

Mar​

[/td][td]

Yes​

[/td][td]

29-jul​

[/td][td]

29-jul​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

11​

[/td][td]

Mar​

[/td][td]

Yes​

[/td][td]

29-Aug​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

Criteria in H2:I2

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

M.