Formula to count "blank" learners with reference to other cells

[LisaCol]

I have currently got a table that refers to a data spreadsheet to count learners, I have got the formula set up when a column contains Green, Amber or Red but I can't figure out what the formula should be if the column is blank.

The formula I currently have in J9 is:=COUNTIFS(Learners!$K:$K,Summary!I$8,Learners!$T:$T,Summary!$B9,Learners!$A:$A,">=2018-2019",Learners!$AY:$AY,Summary!$B$5), where Summary is the spreadsheet below and Learners is the spreadsheet with the data.

​

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD]Supply Chain[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD][/TD]
[TD]Course[/TD]
[TD]Live[/TD]
[TD]Comp[/TD]
[TD]Leaver[/TD]
[TD]Blank[/TD]
[TD]Green[/TD]
[TD]Amber[/TD]
[TD]Red[/TD]
[TD]Blank[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD][/TD]
[TD]Level 2 Ware[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

[/TD]
[/TR]
</tbody>[/TABLE]

​

 

[AlanY]

have you tried the =countblank() function?

 

[LisaCol]

I have tried countblank but then it says that I have entered too many arguments

 

[AlanY]

could you post the formula used here?

 

[LisaCol]

=COUNTIFS(Learners!$K:$K,Summary!I$8,Learners!$T:$T,Summary!$B9,Learners!$A:$A,">=2018-2019",Learners!$AY:$AY,Summary!$B$5)

Summary is the spreadsheet with the table and Learners is the spreadsheet with the data.

Thank you
​

 

[AlanY]

actually i was asking the formula got you the error.
never mind, lets try the sumproduct() instead

the sumproduct equivalent of your formula is

=SUMPRODUCT(--(Learners!K:K=Summary!I8),--(Learners!T:T=Summary!B9),--(Learners!A:A=">=2018-2019"),--(Learners!AY:AY=Summary!B5))

so, to include the check on blank on Col B, say

=SUMPRODUCT(--(Learners!K:K=Summary!I8),--(Learners!T:T=Summary!B9),--(Learners!A:A=">=2018-2019"),--(Learners!AY:AY=Summary!B5)[COLOR="#FF0000"],--(ISBLANK(Learners!B:B))[/COLOR])

 

[LisaCol]

Sorry about that, I have tried the above but it says that there is a problem with the formula.

 

[AlanY]

the only thing i can think of is the cells in Learners!A:A, could you post a small sample of them here please

it would be easier if you can share the excel sheet on google drive or similar

 

[LisaCol]

I deleted that part of the formula to see if that helped and now the formula works, but the output is #N/A. Unfortunately I can't upload the spreadsheet as the data is protected under GDPR, the only thing I could do is upload a anonymised dataset which I could sort out tomorrow.

 

[AlanY]

i created a mock up sheets, have a look to see if that make any sense

Book1
	A	B	K	T	AX	AY
1			RED
2			RED
3			RED	Level 2 Ware
4	2018		RED	Level 2 Ware		Supply Chain
5	2018	xxxx	RED	Level 2 Ware		Supply Chain
6	2019		RED	Level 2 Ware		Supply Chain
7			RED	Level 2 Ware
8			RED	Level 2 Ware
9			RED
10			RED
Learners

Book1
	A	B	C	D	E	F	G	H	I	J
1
2
3
4
5		Supply Chain
6
7
8		Course	Live	Comp	Leaver	Blank	Green	Amber	Red	Blank
9		Level 2 Ware							1
Summary

Cell Formulas
Range	Formula
I9	=SUMPRODUCT(--(Learners!K:K=Summary!I8),--(Learners!T:T=Summary!B9),--(Learners!A:A=2018),--(Learners!AY:AY=Summary!B5),--(ISBLANK(Learners!B:B)))