If sumproduct formula question

**sans** · 11-18-2011, 10:14 AM

Hello,

I am encountering a small problem with the following formula and I can't seem to get it to function correctly. The range is FD4:FM4, the values are numbers (which are obtained by other formulas) while some of the cells are blanks. Here is the formula,

=IF(SUMPRODUCT(--(FD4>=0)*(FD4<=1)+(FE4>=0)*(FE4<=2)+(FF4>=0)*(FF4<=1)+(FG4>=0)*(FG4<=2)+(FH4>=0)*(FH4<=1)+(FI4>=0)*(FI4<=2)+(FJ4>=0)*(FJ4<=1)+(FK4>=0)*(FK4<=2)+(FL4>=0)*(FL4<=1)+(FM4>=0)*(FM4<=1)),CHAR(252),CHAR(251))

I am looking to get a checkmark if

FD4 is between 0 and 1
FE4 0 and 2
FF4 0 and 1
FG4 0 and 2
FH4 0 and 1
FI4 0 and 2
FJ4 0 and 1
FK4 0 and 2
FL4 0 and 1
FM4 0 and 2

Also, generally speaking, does excel see the any blank cells as zeros?

Thank you,
Sans

**edvwvw** · 11-18-2011, 10:25 AM

You could use Boolean - it may be easier to read

=AND(FD4>=0,FD4<=1,FE4>=0,FE4<=2 etc etc)

edvwvw

**NBVC** · 11-18-2011, 10:26 AM

yes the blanks are seen as 0's here...

Try first replacing all the + to * in the formula

and for each cell add another condition, e.g. =SUMPRODUCT((FD4<>"")*(FD4>=0)*(FD4<=1)*(FE4<>"")*(FE4>=0)*(FE4<=2)*.....

**sans** · 11-18-2011, 10:37 AM

Thank you for replying. I have tried both

=IF(SUMPRODUCT(--(FD4<>"")*(FD4>=0)*(FD4<=1)*(FE4<>"")*(FE4>=0)*(FE4<=2)*(FF4<>"")*(FF4>=0)*(FF4<=1)*(FG4<>"")*(FG4>=0)*(FG4<=2)*(FH4<>"")*(FH4>=0)*(FH4<=1)*(FI4<>"")*(FI4>=0)*(FI4<=2)*(FJ4<>"")*(FJ4>=0)*(FJ4<=1)*(FK4<>"")*(FK4>=0)*(FK4<=2)*(FL4<>"")*(FL4>=0)*(FL4<=1)*(FM4<>"")*(FM4>=0)*(FM4<=1)),CHAR(252),CHAR(251))

and

=IF(AND(FD4>=0,FD4<=1,FE4>=0,FE4<=2,FF4>=0,FF4<=1,FG4>=0,FG4<=1),"Yes","No")

but I am getting incorrect results for some reason. Is it possible that the formulas in the cells FD4:FM4 can be a problem?

Thank you,
Sans

**edvwvw** · 11-18-2011, 10:42 AM

sans

You will need to make some changes to my solution - if it is going to be the same as NBVC.

=(AND(FD4<>"", FD4>=0, FD4<=1, etc, etc, etc
edvwvw

**NBVC** · 11-18-2011, 10:48 AM

What kind of formulas do you have in that range?

**sans** · 11-18-2011, 10:57 AM

This is the formula,

=IF(COUNTIF(KT4:KZ4,1)=0,"",COUNTIF(KT4:KZ4,1)))
=IF(COUNTIF(KT4:KZ4,2)=0,"",COUNTIF(KT4:KZ4,2)))...

in all of the cells.

Thank you, Sans

**NBVC** · 11-18-2011, 11:01 AM

It seems to work for me in my testing...

can you post a sample workbook indicating where it is going wrong?

**sans** · 11-18-2011, 11:20 AM

I also copied only the values of FD4:FM4 to a new sheet but still I am encountering a problem.

I have attached a sample. Thank you,
Sans

**NBVC** · 11-18-2011, 12:06 PM

I thought all the cells have to have a number between 0 and 1 or 2 to get a checkmark and so that if any have a blank then you should get an x. Did I misinterpret?

**NBVC** · 11-18-2011, 12:27 PM

Maybe you want?

=IF(SUMPRODUCT((N(D3)>=0)*(N(D3)<=1)*(N(E3)>=0)*(N(E3)<=2)*(N(F3)>=0)*(N(F3)<=1)*(N(G3)>=0)*(N(G3)<=2)*(N(H3)>=0)*(N(H3)<=1)*(N(I3)>=0)*(N(I3)<=2)*(N(J3)>=0)*(N(J3)<=1)*(N(K3)>=0)*(N(K3)<=2)*(N(L3)>=0)*(N(L3)<=1)*(N(M3)>=0)*(N(M3)<=1)),CHAR(252),CHAR(251))

**sans** · 11-18-2011, 04:56 PM

Sorry about that, I just read my original post and it's not clear indeed.

The formula works great. Thank you. It's the first time I see the N used in a formula so I checked and found that the N converts a value to a number. Did you use it because the cells were obtained by formulas?
Thank you for your help,
Sans