formula to offset from specific text and countif function

T

toony

Board Regular
Joined
Mar 20, 2014
Messages
91
Office Version
  1. 365
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

BCDEFG
201/01/2014MaxNO 353
301/01/2014Smith (Sub)YES2 54
401/01/2014Nortor (Sub)YES 54
501/01/2014Custis (Sub)YES1 55
602/01/2014BarloNO 156
702/01/2014Conie (Sub)YES1 57
<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> </tbody>

<colgroup><col width="835" style="width: 626pt; mso-width-source: userset; mso-width-alt: 30537;"> <tbody> </tbody>
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
Sort by date Sort by votes
formula in column F
1/1/2014MaxNO 53=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)))
1/1/2014Smith (Sub)YES2354=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)))
1/1/2014Nortor (Sub)YES 54=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)))
1/1/2014Custis (Sub)YES155=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)))
2/1/2014BarloNO 56=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)))
2/1/2014Conie (Sub)YES1157=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)))
<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> </tbody>
 
Last edited:
Upvote 0
formula in column F
1/1/2014MaxNO 53=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)))
1/1/2014Smith (Sub)YES2354=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)))
1/1/2014Nortor (Sub)YES 54=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)))
1/1/2014Custis (Sub)YES155=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)))
2/1/2014BarloNO 56=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)))
2/1/2014Conie (Sub)YES1157=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)))
<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> </tbody>
 
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
formula in column F
1/1/2014MaxNO 53=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)))
1/1/2014Smith (Sub)YES2354=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)))
1/1/2014Nortor (Sub) 54=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)))
1/1/2014Custis (Sub)YES155=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)))
2/1/2014BarloNO 56=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)))
2/1/2014Conie (Sub)YES1157=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)))
SUMPRODUCT(--(B2=B2:$B$7),E2:E$7)
"B2=B2:$B$7" will give you TRUE and or FALSE
SUMPRODUCT(--({TRUE;TRUE;TRUE;TRUE;FALSE;FALSE}),E2:E$7)
THE"--" will turn the TRUE & FALSE into 1 & 0's.
SUMPRODUCT({1;1;1;1;0;0},E2:E$7)
sumproduct will multiply and then add up the result.
SUMPRODUCT({1;1;1;1;0;0},{0;2;0;1;0;1})
In this case the result is three.
3
Formula in column "D" (maybe this helps)
=IF(SUMPRODUCT(--(G2=G$2:G2)) > 1,"",IF(ISNUMBER(SEARCH("sub",C2)),"YES","NO"))
=IF(SUMPRODUCT(--(G3=G$2:G3)) > 1,"",IF(ISNUMBER(SEARCH("sub",C3)),"YES","NO"))
=IF(SUMPRODUCT(--(G4=G$2:G4)) > 1,"",IF(ISNUMBER(SEARCH("sub",C4)),"YES","NO"))
=IF(SUMPRODUCT(--(G5=G$2:G5)) > 1,"",IF(ISNUMBER(SEARCH("sub",C5)),"YES","NO"))
=IF(SUMPRODUCT(--(G6=G$2:G6)) > 1,"",IF(ISNUMBER(SEARCH("sub",C6)),"YES","NO"))
=IF(SUMPRODUCT(--(G7=G$2:G7)) > 1,"",IF(ISNUMBER(SEARCH("sub",C7)),"YES","NO"))
<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> </tbody>
 
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
Help with FilterXML formula
Replies
0
Views
1K
montex01
M
K
  • Question
Dynamic Address for Use in Function Always returns ERROR
Replies
3
Views
353
Eric W
Eric W
G
  • 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
424
Greenbehindthecells
G
W
  • Question
Personnel Roster using Macro's
Replies
0
Views
225
womeclr
W
G
  • Question
Conditional formating where there are 2 values in a cell
Replies
3
Views
985
Godders199
G

Forum statistics

Threads
1,217,382
Messages
6,136,231
Members
450,000
Latest member
jgp19

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