formula to offset from specific text and countif function

T

toony

Board Regular
Joined
Mar 20, 2014
Messages
91
Office Version
  1. 365
[TABLE="width: 626"]
<colgroup><col width="835" style="width: 626pt; mso-width-source: userset; mso-width-alt: 30537;"> <tbody>[TR]
[TD="width: 835, bgcolor: transparent"]In F2, I have entered formula IF(B2="","",IF(COUNTIF(B$2:B2,B2)=1,SUMIF($B$2:$B$7,$B2,E$2:$E7),"")) to give the sum of E2. I have dragged it down to F7 to give the sum of column E, per day. But I need it offset on the first row where the letters "sub" appear. F3 an F7 cells instead of F2 and F6. So, I added the offset function. Also, if you realised, in cell D, if there two subs with the same code as column G, it says YES twice. Is there a way to have it only once. n D2 I have used formula IF(ISNUMBER(SEARCH("sub",E1916)),"YES","NO") and dragged it down. I have tried using the countif function taking column G as a reference but it doesn't work. Tried different ways and can't work it out. Would really appreciate your help with this. Many thanks

[TABLE="width: 258"]
<colgroup><col width="42" style="width: 32pt; mso-width-source: userset; mso-width-alt: 1536;"> <col width="84" style="width: 63pt; mso-width-source: userset; mso-width-alt: 3072;"> <col width="88" style="width: 66pt; mso-width-source: userset; mso-width-alt: 3218;"> <col width="50" style="width: 38pt; mso-width-source: userset; mso-width-alt: 1828;"> <col width="27" style="width: 20pt; mso-width-source: userset; mso-width-alt: 987;"> <col width="25" style="width: 19pt; mso-width-source: userset; mso-width-alt: 914;"> <col width="26" style="width: 20pt; mso-width-source: userset; mso-width-alt: 950;"> <tbody>[TR]
[TD="width: 42, bgcolor: transparent"] [/TD]
[TD="width: 84, bgcolor: transparent"]B[/TD]
[TD="width: 88, bgcolor: transparent"]C[/TD]
[TD="width: 50, bgcolor: transparent"]D[/TD]
[TD="width: 27, bgcolor: transparent"]E[/TD]
[TD="width: 25, bgcolor: transparent"]F[/TD]
[TD="width: 26, bgcolor: transparent"]G[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent, align: right"]01/01/2014[/TD]
[TD="bgcolor: transparent"]Max[/TD]
[TD="bgcolor: transparent"]NO[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: yellow"]3[/TD]
[TD="bgcolor: transparent, align: right"]53[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]3[/TD]
[TD="bgcolor: transparent, align: right"]01/01/2014[/TD]
[TD="bgcolor: transparent"]Smith (Sub)[/TD]
[TD="bgcolor: transparent"]YES[/TD]
[TD="bgcolor: transparent"]2[/TD]
[TD="bgcolor: yellow"] [/TD]
[TD="bgcolor: transparent, align: right"]54[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]4[/TD]
[TD="bgcolor: transparent, align: right"]01/01/2014[/TD]
[TD="bgcolor: transparent"]Nortor (Sub)[/TD]
[TD="bgcolor: transparent"]YES[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: yellow"] [/TD]
[TD="bgcolor: transparent, align: right"]54[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]5[/TD]
[TD="bgcolor: transparent, align: right"]01/01/2014[/TD]
[TD="bgcolor: transparent"]Custis (Sub)[/TD]
[TD="bgcolor: transparent"]YES[/TD]
[TD="bgcolor: transparent"]1[/TD]
[TD="bgcolor: yellow"] [/TD]
[TD="bgcolor: transparent, align: right"]55[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]6[/TD]
[TD="bgcolor: transparent, align: right"]02/01/2014[/TD]
[TD="bgcolor: transparent"]Barlo[/TD]
[TD="bgcolor: transparent"]NO[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: yellow"]1[/TD]
[TD="bgcolor: transparent, align: right"]56[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]7[/TD]
[TD="bgcolor: transparent, align: right"]02/01/2014[/TD]
[TD="bgcolor: transparent"]Conie (Sub)[/TD]
[TD="bgcolor: transparent"]YES[/TD]
[TD="bgcolor: transparent"]1[/TD]
[TD="bgcolor: yellow"] [/TD]
[TD="bgcolor: transparent, align: right"]57[/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[/TR]
</tbody>[/TABLE]
 
Sort by date Sort by votes
[TABLE="width: 1096"]
<colgroup><col width="30" style="width: 23pt; mso-width-source: userset; mso-width-alt: 1097;"> <col width="102" style="width: 77pt; mso-width-source: userset; mso-width-alt: 3730;"> <col width="130" style="width: 98pt; mso-width-source: userset; mso-width-alt: 4754;"> <col width="61" style="width: 46pt; mso-width-source: userset; mso-width-alt: 2230;"> <col width="40" style="width: 30pt; mso-width-source: userset; mso-width-alt: 1462;"> <col width="37" style="width: 28pt; mso-width-source: userset; mso-width-alt: 1353;"> <col width="64" style="width: 48pt;"> <col width="994" style="width: 746pt; mso-width-source: userset; mso-width-alt: 36352;"> <tbody>[TR]
[TD="width: 30, bgcolor: transparent"][/TD]
[TD="width: 102, bgcolor: transparent"][/TD]
[TD="width: 130, bgcolor: transparent"][/TD]
[TD="width: 61, bgcolor: transparent"][/TD]
[TD="width: 40, bgcolor: transparent"][/TD]
[TD="width: 37, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 994, bgcolor: transparent"]formula in column F[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="width: 102, bgcolor: transparent"]1/1/2014[/TD]
[TD="width: 130, bgcolor: transparent"]Max[/TD]
[TD="width: 61, bgcolor: white"]NO[/TD]
[TD="width: 40, bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"]53[/TD]
[TD="bgcolor: transparent"]=IF(E2'="","",IF(SUMPRODUCT(--(B2'=B2:$B$7),E2:E$7) < SUMPRODUCT(--(B2'=B$2:B2),E$2:E2),"",SUMPRODUCT(--(B2'=B2:$B$7),E2:E$7)))[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="width: 102, bgcolor: transparent"]1/1/2014[/TD]
[TD="width: 130, bgcolor: transparent"]Smith (Sub)[/TD]
[TD="width: 61, bgcolor: transparent"]YES[/TD]
[TD="width: 40, bgcolor: transparent"]2[/TD]
[TD="bgcolor: transparent, align: right"]3[/TD]
[TD="width: 64, bgcolor: transparent"]54[/TD]
[TD="bgcolor: transparent"]=IF(E3'="","",IF(SUMPRODUCT(--(B3'=B3:$B$7),E3:E$7) < SUMPRODUCT(--(B3'=B$2:B3),E$2:E3),"",SUMPRODUCT(--(B3'=B3:$B$7),E3:E$7)))[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="width: 102, bgcolor: transparent"]1/1/2014[/TD]
[TD="width: 130, bgcolor: transparent"]Nortor (Sub)[/TD]
[TD="width: 61, bgcolor: transparent"]YES[/TD]
[TD="width: 40, bgcolor: white"] [/TD]
[TD="bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"]54[/TD]
[TD="bgcolor: transparent"]=IF(E4'="","",IF(SUMPRODUCT(--(B4'=B4:$B$7),E4:E$7) < SUMPRODUCT(--(B4'=B$2:B4),E$2:E4),"",SUMPRODUCT(--(B4'=B4:$B$7),E4:E$7)))[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="width: 102, bgcolor: transparent"]1/1/2014[/TD]
[TD="width: 130, bgcolor: transparent"]Custis (Sub)[/TD]
[TD="width: 61, bgcolor: transparent"]YES[/TD]
[TD="width: 40, bgcolor: transparent"]1[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"]55[/TD]
[TD="bgcolor: transparent"]=IF(E5'="","",IF(SUMPRODUCT(--(B5'=B5:$B$7),E5:E$7) < SUMPRODUCT(--(B5'=B$2:B5),E$2:E5),"",SUMPRODUCT(--(B5'=B5:$B$7),E5:E$7)))[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="width: 102, bgcolor: transparent"]2/1/2014[/TD]
[TD="width: 130, bgcolor: transparent"]Barlo[/TD]
[TD="width: 61, bgcolor: transparent"]NO[/TD]
[TD="width: 40, bgcolor: white"] [/TD]
[TD="bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"]56[/TD]
[TD="bgcolor: transparent"]=IF(E6'="","",IF(SUMPRODUCT(--(B6'=B6:$B$7),E6:E$7) < SUMPRODUCT(--(B6'=B$2:B6),E$2:E6),"",SUMPRODUCT(--(B6'=B6:$B$7),E6:E$7)))[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="width: 102, bgcolor: transparent"]2/1/2014[/TD]
[TD="width: 130, bgcolor: transparent"]Conie (Sub)[/TD]
[TD="width: 61, bgcolor: transparent"]YES[/TD]
[TD="width: 40, bgcolor: transparent"]1[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="width: 64, bgcolor: transparent"]57[/TD]
[TD="bgcolor: transparent"]=IF(E7'="","",IF(SUMPRODUCT(--(B7'=B7:$B$7),E7:E$7) < SUMPRODUCT(--(B7'=B$2:B7),E$2:E7),"",SUMPRODUCT(--(B7'=B7:$B$7),E7:E$7)))[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]
 
Last edited:
Upvote 0
[TABLE="width: 1096"]
<colgroup><col width="30" style="width: 23pt; mso-width-source: userset; mso-width-alt: 1097;"> <col width="102" style="width: 77pt; mso-width-source: userset; mso-width-alt: 3730;"> <col width="130" style="width: 98pt; mso-width-source: userset; mso-width-alt: 4754;"> <col width="61" style="width: 46pt; mso-width-source: userset; mso-width-alt: 2230;"> <col width="40" style="width: 30pt; mso-width-source: userset; mso-width-alt: 1462;"> <col width="37" style="width: 28pt; mso-width-source: userset; mso-width-alt: 1353;"> <col width="64" style="width: 48pt;"> <col width="994" style="width: 746pt; mso-width-source: userset; mso-width-alt: 36352;"> <tbody>[TR]
[TD="width: 30, bgcolor: transparent"][/TD]
[TD="width: 102, bgcolor: transparent"][/TD]
[TD="width: 130, bgcolor: transparent"][/TD]
[TD="width: 61, bgcolor: transparent"][/TD]
[TD="width: 40, bgcolor: transparent"][/TD]
[TD="width: 37, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 994, bgcolor: transparent"]formula in column F[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="width: 102, bgcolor: transparent"]1/1/2014[/TD]
[TD="width: 130, bgcolor: transparent"]Max[/TD]
[TD="width: 61, bgcolor: white"]NO[/TD]
[TD="width: 40, bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"]53[/TD]
[TD="bgcolor: transparent"]=IF(E2="","",IF(SUMPRODUCT(--(B2=B2:$B$7),E2:E$7) < SUMPRODUCT(--(B2=B$2:B2),E$2:E2),"",SUMPRODUCT(--(B2=B2:$B$7),E2:E$7)))[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="width: 102, bgcolor: transparent"]1/1/2014[/TD]
[TD="width: 130, bgcolor: transparent"]Smith (Sub)[/TD]
[TD="width: 61, bgcolor: transparent"]YES[/TD]
[TD="width: 40, bgcolor: transparent"]2[/TD]
[TD="bgcolor: transparent, align: right"]3[/TD]
[TD="width: 64, bgcolor: transparent"]54[/TD]
[TD="bgcolor: transparent"]=IF(E3="","",IF(SUMPRODUCT(--(B3=B3:$B$7),E3:E$7) < SUMPRODUCT(--(B3=B$2:B3),E$2:E3),"",SUMPRODUCT(--(B3=B3:$B$7),E3:E$7)))[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="width: 102, bgcolor: transparent"]1/1/2014[/TD]
[TD="width: 130, bgcolor: transparent"]Nortor (Sub)[/TD]
[TD="width: 61, bgcolor: transparent"]YES[/TD]
[TD="width: 40, bgcolor: white"] [/TD]
[TD="bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"]54[/TD]
[TD="bgcolor: transparent"]=IF(E4="","",IF(SUMPRODUCT(--(B4=B4:$B$7),E4:E$7) < SUMPRODUCT(--(B4=B$2:B4),E$2:E4),"",SUMPRODUCT(--(B4=B4:$B$7),E4:E$7)))[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="width: 102, bgcolor: transparent"]1/1/2014[/TD]
[TD="width: 130, bgcolor: transparent"]Custis (Sub)[/TD]
[TD="width: 61, bgcolor: transparent"]YES[/TD]
[TD="width: 40, bgcolor: transparent"]1[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"]55[/TD]
[TD="bgcolor: transparent"]=IF(E5="","",IF(SUMPRODUCT(--(B5=B5:$B$7),E5:E$7) < SUMPRODUCT(--(B5=B$2:B5),E$2:E5),"",SUMPRODUCT(--(B5=B5:$B$7),E5:E$7)))[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="width: 102, bgcolor: transparent"]2/1/2014[/TD]
[TD="width: 130, bgcolor: transparent"]Barlo[/TD]
[TD="width: 61, bgcolor: transparent"]NO[/TD]
[TD="width: 40, bgcolor: white"] [/TD]
[TD="bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"]56[/TD]
[TD="bgcolor: transparent"]=IF(E6="","",IF(SUMPRODUCT(--(B6=B6:$B$7),E6:E$7) < SUMPRODUCT(--(B6=B$2:B6),E$2:E6),"",SUMPRODUCT(--(B6=B6:$B$7),E6:E$7)))[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="width: 102, bgcolor: transparent"]2/1/2014[/TD]
[TD="width: 130, bgcolor: transparent"]Conie (Sub)[/TD]
[TD="width: 61, bgcolor: transparent"]YES[/TD]
[TD="width: 40, bgcolor: transparent"]1[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="width: 64, bgcolor: transparent"]57[/TD]
[TD="bgcolor: transparent"]=IF(E7="","",IF(SUMPRODUCT(--(B7=B7:$B$7),E7:E$7) < SUMPRODUCT(--(B7=B$2:B7),E$2:E7),"",SUMPRODUCT(--(B7=B7:$B$7),E7:E$7)))
[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Hi,

Many, many thanks for your help and to create the above formula. Have only tried it with a small example like the one posted and it works absolutely fine However, the formula looks very cool and would love to understand the logic of the formula in an excel jargon. Ie, never sow before the two dashes together. Also, originally I also was querying how to get the YES into once if same code number in column E is duplicated, other than a YES for each duplicated code. Tried countif, but I don't think it likes text. Also tried combining countif with the isnumber function but not getting there either. Would really appreciate your help
 
Upvote 0
[TABLE="width: 1067"]
<colgroup><col width="102" style="width: 77pt; mso-width-source: userset; mso-width-alt: 3730;"> <col width="130" style="width: 98pt; mso-width-source: userset; mso-width-alt: 4754;"> <col width="61" style="width: 46pt; mso-width-source: userset; mso-width-alt: 2230;"> <col width="40" style="width: 30pt; mso-width-source: userset; mso-width-alt: 1462;"> <col width="37" style="width: 28pt; mso-width-source: userset; mso-width-alt: 1353;"> <col width="64" style="width: 48pt;"> <col width="986" style="width: 740pt; mso-width-source: userset; mso-width-alt: 36059;"> <tbody>[TR]
[TD="width: 102, bgcolor: transparent"][/TD]
[TD="width: 130, bgcolor: transparent"][/TD]
[TD="width: 61, bgcolor: transparent"][/TD]
[TD="width: 40, bgcolor: transparent"][/TD]
[TD="width: 37, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 986, bgcolor: transparent"]formula in column F[/TD]
[/TR]
[TR]
[TD="width: 102, bgcolor: white"]1/1/2014[/TD]
[TD="width: 130, bgcolor: white"]Max[/TD]
[TD="width: 61, bgcolor: white"]NO[/TD]
[TD="width: 40, bgcolor: white"] [/TD]
[TD="bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: white"]53[/TD]
[TD="bgcolor: transparent"]=IF(E2="","",IF(SUMPRODUCT(--(B2=B2:$B$7),E2:E$7) < SUMPRODUCT(--(B2=B$2:B2),E$2:E2),"",SUMPRODUCT(--(B2=B2:$B$7),E2:E$7)))[/TD]
[/TR]
[TR]
[TD="width: 102, bgcolor: white"]1/1/2014[/TD]
[TD="width: 130, bgcolor: white"]Smith (Sub)[/TD]
[TD="width: 61, bgcolor: white"]YES[/TD]
[TD="width: 40, bgcolor: white"]2[/TD]
[TD="bgcolor: transparent, align: right"]3[/TD]
[TD="width: 64, bgcolor: white"]54[/TD]
[TD="bgcolor: transparent"]=IF(E3="","",IF(SUMPRODUCT(--(B3=B3:$B$7),E3:E$7) < SUMPRODUCT(--(B3=B$2:B3),E$2:E3),"",SUMPRODUCT(--(B3=B3:$B$7),E3:E$7)))[/TD]
[/TR]
[TR]
[TD="width: 102, bgcolor: white"]1/1/2014[/TD]
[TD="width: 130, bgcolor: white"]Nortor (Sub)[/TD]
[TD="width: 61, bgcolor: white"] [/TD]
[TD="width: 40, bgcolor: white"] [/TD]
[TD="bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: white"]54[/TD]
[TD="bgcolor: transparent"]=IF(E4="","",IF(SUMPRODUCT(--(B4=B4:$B$7),E4:E$7) < SUMPRODUCT(--(B4=B$2:B4),E$2:E4),"",SUMPRODUCT(--(B4=B4:$B$7),E4:E$7)))[/TD]
[/TR]
[TR]
[TD="width: 102, bgcolor: white"]1/1/2014[/TD]
[TD="width: 130, bgcolor: white"]Custis (Sub)[/TD]
[TD="width: 61, bgcolor: white"]YES[/TD]
[TD="width: 40, bgcolor: white"]1[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: white"]55[/TD]
[TD="bgcolor: transparent"]=IF(E5="","",IF(SUMPRODUCT(--(B5=B5:$B$7),E5:E$7) < SUMPRODUCT(--(B5=B$2:B5),E$2:E5),"",SUMPRODUCT(--(B5=B5:$B$7),E5:E$7)))[/TD]
[/TR]
[TR]
[TD="width: 102, bgcolor: white"]2/1/2014[/TD]
[TD="width: 130, bgcolor: white"]Barlo[/TD]
[TD="width: 61, bgcolor: white"]NO[/TD]
[TD="width: 40, bgcolor: white"] [/TD]
[TD="bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: white"]56[/TD]
[TD="bgcolor: transparent"]=IF(E6="","",IF(SUMPRODUCT(--(B6=B6:$B$7),E6:E$7) < SUMPRODUCT(--(B6=B$2:B6),E$2:E6),"",SUMPRODUCT(--(B6=B6:$B$7),E6:E$7)))[/TD]
[/TR]
[TR]
[TD="width: 102, bgcolor: white"]2/1/2014[/TD]
[TD="width: 130, bgcolor: white"]Conie (Sub)[/TD]
[TD="width: 61, bgcolor: white"]YES[/TD]
[TD="width: 40, bgcolor: white"]1[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="width: 64, bgcolor: white"]57[/TD]
[TD="bgcolor: transparent"]=IF(E7="","",IF(SUMPRODUCT(--(B7=B7:$B$7),E7:E$7) < SUMPRODUCT(--(B7=B$2:B7),E$2:E7),"",SUMPRODUCT(--(B7=B7:$B$7),E7:E$7)))[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]SUMPRODUCT(--(B2=B2:$B$7),E2:E$7)[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]"B2=B2:$B$7" will give you TRUE and or FALSE[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]SUMPRODUCT(--({TRUE;TRUE;TRUE;TRUE;FALSE;FALSE}),E2:E$7)[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]THE"--" will turn the TRUE & FALSE into 1 & 0's.[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]SUMPRODUCT({1;1;1;1;0;0},E2:E$7)[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]sumproduct will multiply and then add up the result.[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]SUMPRODUCT({1;1;1;1;0;0},{0;2;0;1;0;1})[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]In this case the result is three.[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]3[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]Formula in column "D" (maybe this helps)[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]=IF(SUMPRODUCT(--(G2=G$2:G2)) > 1,"",IF(ISNUMBER(SEARCH("sub",C2)),"YES","NO"))[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]=IF(SUMPRODUCT(--(G3=G$2:G3)) > 1,"",IF(ISNUMBER(SEARCH("sub",C3)),"YES","NO"))[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]=IF(SUMPRODUCT(--(G4=G$2:G4)) > 1,"",IF(ISNUMBER(SEARCH("sub",C4)),"YES","NO"))[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]=IF(SUMPRODUCT(--(G5=G$2:G5)) > 1,"",IF(ISNUMBER(SEARCH("sub",C5)),"YES","NO"))[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]=IF(SUMPRODUCT(--(G6=G$2:G6)) > 1,"",IF(ISNUMBER(SEARCH("sub",C6)),"YES","NO"))[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]=IF(SUMPRODUCT(--(G7=G$2:G7)) > 1,"",IF(ISNUMBER(SEARCH("sub",C7)),"YES","NO"))[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Thank you for your extensive explanation. It's much clearer now. Great!
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question Question
Help with FilterXML formula
Replies
0
Views
2K
montex01
M
K
  • Question Question
Dynamic Address for Use in Function Always returns ERROR
Replies
3
Views
607
Eric W
Eric W
G
  • Question Question
Formula to count with multiple criteria and exclude from a separate column, then sort by count in one cell with spill
Replies
0
Views
653
Greenbehindthecells
G
W
  • Question Question
Personnel Roster using Macro's
Replies
0
Views
342
womeclr
W
G
  • Question Question
Conditional formating where there are 2 values in a cell
Replies
3
Views
1K
Godders199
G

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top