Ignore blank cells when using Sumproduct formula

[jjwen]

Hi everyone, I have a problem when using Sumproduct formula:

In cell B16, I' m trying to count the number of cells where the value in Column B is less than or equal to the value in Column A, but only when there's a value in Column B.

For example, the value in B1, B2, B3, B4, B6, B12 and B14 is less than or equal to the value in A1, A2, A3, A4, A6, A12 and A14, so there's 7 cells in column B that meets the criteria, and it should return 7 in B13. However, if I use =SUMPRODUCT(--(A1:A15>=B1:B15)) to do the calculation, it will include blank cells in column B and return 13 in B13. Is there a formula to ignore the blank cells in column B? Thank you in advance for your help!

 

[kevin9999]

Book1
	A	B
1	3	1
2	2	2
3	4	3
4	6	5
5	7
6	4	4
7	8
8	5
9	3	4
10	6
11	2	6
12	9	7
13	7
14	6	5
15	5
16		7
Sheet1

Cell Formulas
Range	Formula
B16	B16	=SUMPRODUCT((B1:B15<>"")*(B1:B15<=A1:A15))

 

[Peter_SSs]

Welcome to the MrExcel board!

Another option might be to use some of the new functions available to you in MS365.

23 06 04.xlsm
	A	B
1	3	1
2	2	2
3	4	3
4	6	5
5	7
6	4	4
7	8
8	5
9	3	4
10	6
11	2	6
12	9	7
13	7
14	6	5
15	5
16		7
COUNTIFS

Cell Formulas
Range	Formula
B16	B16	=LET(b,B1:B15,COUNT(FILTER(b,(b<>"")*(b<=A1:A15),"")))

 

[Alex Blakenburg]

Converting @kevin9999 formula to take advantage of MS365 would give you this:

=LET(b,B1:B15,SUM((b<>"")*(b<=A1:A15)))

 

[Peter_SSs]

Just realised that with my count/filter suggestion, there is no need for the 'blank' check or for the 'if empty' filter provision. This does the same job.

23 06 04.xlsm
	A	B
1	3	1
2	2	2
3	4	3
4	6	5
5	7
6	4	4
7	8
8	5
9	3	4
10	6
11	2	6
12	9	7
13	7
14	6	5
15	5
16		7
COUNTIFS

Cell Formulas
Range	Formula
B16	B16	=COUNT(FILTER(B1:B15,B1:B15<=A1:A15))