Another Blank Cell query

[Matt Cook]

Hi

I've got some great info from this forum however I cannot find the answer to my current question, Although I'm sure it's been asked before.

I have a column 'M', containing either, Yes, No or 'blank'

I am using the following COUNTIFS formula to get the info I need but now I need to add the blank cells into the result when searching for a 'Yes.

=COUNTIFS('Job Log'!H:H,">="&A2,'Job Log'!H:H,"<"&A3,'Job Log'!M:M,"Yes",'Job Log'!F:F,"AW7212 Lincoln")

Can somebody help me please?

Thanks

 

[offthelip]

trythis ( Just copy and paste your equation and changed "Yes" to "")

=COUNTIFS('Job Log'!H:H,">="&A2,'Job Log'!H:H,"<"&A3,'Job Log'!M:M,"Yes",'Job Log'!F:F,"AW7212 Lincoln")+COUNTIFS('Job Log'!H:H,">="&A2,'Job Log'!H:H,"<"&A3,'Job Log'!M:M,"",'Job Log'!F:F,"AW7212 Lincoln")

 

[kevin9999]

Another option

=SUM(COUNTIFS('Job Log'!H:H,">"&A2,'Job Log'!H:H,"<"&A3,'Job Log'!F:F,"AW7212 Lincoln",'Job Log'!M:M,{"Yes",""}))

 

[Matt Cook]

trythis ( Just copy and paste your equation and changed "Yes" to "")

=COUNTIFS('Job Log'!H:H,">="&A2,'Job Log'!H:H,"<"&A3,'Job Log'!M:M,"Yes",'Job Log'!F:F,"AW7212 Lincoln")+COUNTIFS('Job Log'!H:H,">="&A2,'Job Log'!H:H,"<"&A3,'Job Log'!M:M,"",'Job Log'!F:F,"AW7212 Lincoln")

This works a treat but only when the first COUNTIFS is looking for the blank cell.

Thanks so much for this, I didn't know that I could add formulas together in that way. I feel you've shown me so much more than just the solution to my query.

 

[Matt Cook]

Another option

=SUM(COUNTIFS('Job Log'!H:H,">"&A2,'Job Log'!H:H,"<"&A3,'Job Log'!F:F,"AW7212 Lincoln",'Job Log'!M:M,{"Yes",""}))

Hi

Thanks so much for this reply.

I couldn't get this formula to work for some reason.

 

[kevin9999]

I couldn't get this formula to work for some reason.

I don't know what your data looks like, but here is what I get when I tested it on a mock up (should result in 3 matches to all the criteria)

Book1
	F	G	H	I	J	K	L	M
1	Column F		Column H					Column M
2			4					Yes
3			5					Yes
4	AW7212 Lincoln		6
5			7					Yes
6	AW7212 Lincoln		8
7	AW7212 Lincoln		9					Yes
8	AW7212 Lincoln		10
9	AW7212 Lincoln		11					Yes
10			12					Yes
Job Log

Book1
	A	B	C
1
2	5	H:H more than this
3	10	H:H less than this
4
5	3	Result of formula
6
Sheet2

Cell Formulas
Range	Formula
A5	A5	=SUM(COUNTIFS('Job Log'!H:H,">"&A2,'Job Log'!H:H,"<"&A3,'Job Log'!F:F,"AW7212 Lincoln",'Job Log'!M:M,{"Yes",""}))

Did you try copying and pasting the formula into the cell where you wanted the result?

 

[Matt Cook]

I don't know what your data looks like, but here is what I get when I tested it on a mock up (should result in 3 matches to all the criteria)

Book1
	F	G	H	I	J	K	L	M
1	Column F		Column H					Column M
2			4					Yes
3			5					Yes
4	AW7212 Lincoln		6
5			7					Yes
6	AW7212 Lincoln		8
7	AW7212 Lincoln		9					Yes
8	AW7212 Lincoln		10
9	AW7212 Lincoln		11					Yes
10			12					Yes
Job Log

Book1
	A	B	C
1
2	5	H:H more than this
3	10	H:H less than this
4
5	3	Result of formula
6
Sheet2

Cell Formulas
Range	Formula
A5	A5	=SUM(COUNTIFS('Job Log'!H:H,">"&A2,'Job Log'!H:H,"<"&A3,'Job Log'!F:F,"AW7212 Lincoln",'Job Log'!M:M,{"Yes",""}))

Did you try copying and pasting the formula into the cell where you wanted the result?

Hi

Yes, I did copy and paste the formula to where it needed to go. I've always had problems within this COUNTIFS when looking for more than one criteria within a criteria range. (The Yes and blank).

The formula below works, so I'm happy I have this functionality working within my spreadsheet.

=COUNTIFS('Job Log'!H:H,">="&A7,'Job Log'!H:H,"<"&A8,'Job Log'!M:M,"",'Job Log'!D:D,"*AW7212 Lincoln")+COUNTIFS('Job Log'!H:H,">="&A7,'Job Log'!H:H,"<"&A8,'Job Log'!M:M,"Yes",'Job Log'!D:D,"AW7212 Lincoln")

H = a week commencing date


Thank you