Excel formula will not account for empty cells

[andrewoneill3]

Hello to everyone on this forum! I am so glad I found this because I am pretty stuck. Here is my problem:

I have a list of names on one excel sheet, I have a formula that takes whatever name is typed into a particular cell and checks the number of times that particular cell appears across a specified range of cells on a different page. The final task is the formula will then display the number of times the name appears across that specified range. The number will display in a cell on the page that also has the specified range where the formula checks the number of times the name appears.

Here is the formula I used:

=SUMPRODUCT((J2:P8=Setup!B26)*1)

My problem is that I don't have the formula recognizing that a cell across a specified range is blank or empty. I have tried several different modifications that did not work:

=SUMPRODUCT((J2:P8=Setup!B26<>"")*1)

=SUMPRODUCT((J2:P8<>""=Setup!B26)*1)

=SUMPRODUCT((J2:P8=Setup!B26)*1<>"")

These are just a few examples. I have tried other methods, but the truth is I don't understand the formulas enough to understand how to even go about thinking about this.

Any help would be so greatly appreciated!

Thank you, Andrew

 

[Jonmo1]

Welcome to the board.

I'm not sure I understand the problem
If a cell in J2:P8 is blank, then surely it doesn't = B26, so it is not counted.

Unless you're talking about only when B26 is blank, in that case you only need to test for that single cell.

=IF(Setup!B26="","",SUMPRODUCT((J2:P8=Setup!B26)*1))

 

[andrewoneill3]

Jonmo1, thank you for help, unfortunately that does not do the trick, I just tried. I will try to explain the problem one more time below.

I have a list of names that can be changed on the sheet called "Setup":
B22 Tom
B23 Alex
B24 Isabel
B25 Mary
B26 "blank" (there is nothing in this cell)

On another sheet, I have a range:
J2:P8

In that range, the names from above are randomly chosen to fill the cells in that range.

Off to the side of the range, I have cells for each name. The cell that counts for B26 counts the number of times the particular name from that cell on "Setup" B26 appears across the range of J2:P8.

The issue is that B26 is blank and the formula is counting all of the blank cells in the range J2:P8.

The best way I can describe this is that the cell B26 is blank, so what my formula is doing is saying, ok, blank is in B26 and I found 39 blanks across the range J2:P8. The cell displays "39" in the box associated with "blank". I would like this cell to remain blank if possible.

I hope oped this is more clear. Again, thanks for such a quick response!

 

[Jonmo1]

It works for me..
Perhaps your B26 cell (and the blanks in J2:P8) are not really blank.
Are those cells the result of another formula?

Excel Workbook
	G	H	I	J	K	L	M	N	O	P
2	Tom	12		Alex	Alex	Isabel	Tom	Mary	Tom	Alex
3	Alex	9		Isabel		Tom	Isabel		Mary	Mary
4	Isabel	9		Tom	Tom	Alex	Mary		Isabel	Isabel
5	Mary	9			Tom		Tom		Tom	Isabel
6				Isabel	Isabel	Tom	Mary	Tom	Isabel
7				Tom			Alex		Mary	Alex
8				Mary	Mary	Alex	Tom	Mary	Alex	Alex
another sheet

Excel Workbook
	B
22	Tom
23	Alex
24	Isabel
25	Mary
26
Setup

 

[andrewoneill3]

ok, that worked perfectly now! It was the $'s that made the difference I think. Thank you so much! I am working on a large scheduling project and this was a big part of the whole thing. Now I am set to continue. Thanks again, that was such a big help!

 

[Jonmo1]

Glad to help, thanks for the feedback.