Hello Community
I would like to request some help for the following
I am looking for a formula that will give a total for the number of times a specific value appears in sequence in a column
My spreadsheet that I need this for is several hundred rows long, so have included a sample. Column A is the data to work with,The correct answers are manually entered in Columns B and C
Hope this makes sense
Thank you in advance
Cheers
Brian
In B2 copied across and down:
=IF(AND($A2=B$1,$A3<>$A2),COUNTIF($A$2:A2,B$1)-SUM(B$1:B1),"")
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
Hi Ali
Wow, Works a treat, still trying to work out how it works!
Thanks so much for taking the time to help me
Regards
Brian
Let me know if you want me to explain it.
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
Also, as a relatively new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.
Thanks for the pointers above
Apologies for posting this as a new thread (I thought I had marked this as solved)
I want to count the number of times "Column B" has a value, while ignoring the values in "Column C" , A value in "Column A" is what halts the sequence. AliGW was good enough to write the formula in yellow for me for a totally different application . I have been trying to get it to work here with helper rows etc , but Im stuck
Answers Im after are in "Column E"
--Thanks in anticipation
Not sure why you ignore B2
Maybe try at E2
=IF((SUMPRODUCT(--(MOD(SUMPRODUCT((B$2:B2=1)*(B$3:B3="")),{99,2})<>{1,0}))=2)*(B2=1)*(B3=""),SUM(B$2:B2)-SUM(E$1:E1)-D$1,"")
Hi Bo_Ry
Thanks for looking at this, Ill try to explain what Im after another way- hope this helps explain what Im after
I want to count the number of times the "1" occurs in "Column B", until a "1" appears in "Column A"
Ive added a bit more data and changed some of the inputs--answers Im after are in "Column F"
(Dont assume Ive copied your formula correctly down--array formulas are something Im not comfortable with---yet)
Thanks in advance
Cheers
Brian
I mark the last row+1 with 1 as ending sign.
Try this array formua in E2:
**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).
Please Login or Register to view this content.
Quang PT
Please try at E2
=IF((A2="")*(A3=1)+(COUNT(A3:C42)=0),IFNA(SUM(INDEX(B$1:B2,MATCH(2,A$1:A2)):B2),""),"")
Thank you for your formula, works well
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
Thanks bebo021999
Your formula works well
Thanks Bo_Ry
This also works well
If you get a chance can you explain how the count A3:C42 works and why it is that size to start with?
Also why does the INDEX and MATCH start in B$1 & A$1 ?
Regards
brian
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks