Microsoft Office Forums

Go Back   Microsoft Office Forums > Microsoft Excel > Excel

Reply
 
LinkBack Thread Tools Display Modes
  #1  
Old 03-29-2019, 05:19 PM
Greggey1 Greggey1 is offline Windows Vista Office 2016
Novice
 
Join Date: Mar 2019
Posts: 10
Greggey1 is on a distinguished road
Default Countif multiple same range

Hi,
Having an issue figuring out the right formula for the following issue.


Inputting range values into B5:B14
Need 9???/?? To = 2
9???/??? To = 2
9??? To = 1
Feel like the right start of the formula would be =sum countif(B:5:B14,
Have no idea how to finish it or if its possible.
Reply With Quote
  #2  
Old 03-29-2019, 06:45 PM
Marcia Marcia is offline Windows 7 32bit Office 2007
Competent Performer
 
Join Date: May 2018
Location: Philippines
Posts: 174
Marcia is on a distinguished road
Default

Are you trying to count the number of occurrences of each data in range B5:B14?, If so, how about this formula in say, C5.

=countif(B$5:B$14,B5)
Reply With Quote
  #3  
Old 03-29-2019, 07:40 PM
Greggey1 Greggey1 is offline Windows Vista Office 2016
Novice
 
Join Date: Mar 2019
Posts: 10
Greggey1 is on a distinguished road
Default

Sorry it's hard for me to explain Im a rookie at this. .
B5:B14 can have three different option in each cell. (9???/??, 9???/???, 9????)
I need the total of B5:B14.

B5-9438/39
B7-9558/659
B14-9564
(Rest of cells are blank)
This would equal 5
Reply With Quote
  #4  
Old 03-29-2019, 10:18 PM
xor xor is offline Windows 10 Office 2016
Expert
 
Join Date: Oct 2015
Posts: 1,007
xor is just really nicexor is just really nicexor is just really nicexor is just really nicexor is just really nice
Default

Maybe like the attached.
Attached Files
File Type: xlsx Multiple_Same.xlsx (10.2 KB, 7 views)
Reply With Quote
  #5  
Old 03-30-2019, 02:48 AM
bosco_yip bosco_yip is offline Windows 10 Office 2016
Novice
 
Join Date: Feb 2019
Location: Singapore
Posts: 7
bosco_yip is on a distinguished road
Default

Or....................

=SUMPRODUCT(0+(LEN(B5:B14)>0)*(LEN(B5:B14)-LEN(SUBSTITUTE(B5:B14,"/",""))+1))

Regards
Bosco
Reply With Quote
  #6  
Old 03-30-2019, 04:34 AM
Greggey1 Greggey1 is offline Windows Vista Office 2016
Novice
 
Join Date: Mar 2019
Posts: 10
Greggey1 is on a distinguished road
Default

Quote:
Originally Posted by xor View Post
Maybe like the attached.
How did you achieve that? I don't see a formula.
Reply With Quote
  #7  
Old 03-30-2019, 04:40 AM
xor xor is offline Windows 10 Office 2016
Expert
 
Join Date: Oct 2015
Posts: 1,007
xor is just really nicexor is just really nicexor is just really nicexor is just really nicexor is just really nice
Default

Formula is in cell D14.
Reply With Quote
  #8  
Old 03-30-2019, 05:14 AM
Greggey1 Greggey1 is offline Windows Vista Office 2016
Novice
 
Join Date: Mar 2019
Posts: 10
Greggey1 is on a distinguished road
Default

[quote=xor;139958]Formula is in cell D14.[/QUOTE
Was looking at it with my phone. That formula is quite impressive. However I typed 9368/69 in b9 and it didn't change the sum.
Reply With Quote
  #9  
Old 03-30-2019, 05:16 AM
Greggey1 Greggey1 is offline Windows Vista Office 2016
Novice
 
Join Date: Mar 2019
Posts: 10
Greggey1 is on a distinguished road
Default

Only time the sum changes is with 9???
Reply With Quote
  #10  
Old 03-30-2019, 05:32 AM
Greggey1 Greggey1 is offline Windows Vista Office 2016
Novice
 
Join Date: Mar 2019
Posts: 10
Greggey1 is on a distinguished road
Default

Quote:
Originally Posted by bosco_yip View Post
Or....................

=SUMPRODUCT(0+(LEN(B5:B14)>0)*(LEN(B5:B14)-LEN(SUBSTITUTE(B5:B14,"/",""))+1))

Regards
Bosco
That's very close. The only thing is if someone who enters a number not beginning with 9 I don't want it to count. Thanks for the help.
Reply With Quote
  #11  
Old 03-30-2019, 07:17 AM
xor xor is offline Windows 10 Office 2016
Expert
 
Join Date: Oct 2015
Posts: 1,007
xor is just really nicexor is just really nicexor is just really nicexor is just really nicexor is just really nice
Default

Maybe this one is better?
Attached Files
File Type: xlsx Multiple_Same_2.xlsx (10.2 KB, 2 views)
Reply With Quote
  #12  
Old 03-30-2019, 07:29 AM
Greggey1 Greggey1 is offline Windows Vista Office 2016
Novice
 
Join Date: Mar 2019
Posts: 10
Greggey1 is on a distinguished road
Default

Now the sum changes with 9???/?? And 9???/??? But not with 9??? The opposite of the first formula. Really appreciate the help.
Reply With Quote
  #13  
Old 03-30-2019, 08:44 AM
bosco_yip bosco_yip is offline Windows 10 Office 2016
Novice
 
Join Date: Feb 2019
Location: Singapore
Posts: 7
bosco_yip is on a distinguished road
Default

Quote:
Originally Posted by Greggey1 View Post
That's very close. The only thing is if someone who enters a number not beginning with 9 I don't want it to count. Thanks for the help.
Then, the formula changed to

=SUMPRODUCT(ISNUMBER(1/(0+LEFT(B5:B14)=9))*(LEN(B5:B14)-LEN(SUBSTITUTE(B5:B14,"/",""))+1))

Regards
Bosco
Reply With Quote
  #14  
Old 03-30-2019, 09:36 AM
xor xor is offline Windows 10 Office 2016
Expert
 
Join Date: Oct 2015
Posts: 1,007
xor is just really nicexor is just really nicexor is just really nicexor is just really nicexor is just really nice
Default

I will try once more.
Attached Files
File Type: xlsx Multiple_Same_4.xlsx (11.0 KB, 3 views)

Last edited by xor; 03-30-2019 at 06:41 PM. Reason: Uploaded new file.
Reply With Quote
  #15  
Old 03-30-2019, 11:07 AM
Greggey1 Greggey1 is offline Windows Vista Office 2016
Novice
 
Join Date: Mar 2019
Posts: 10
Greggey1 is on a distinguished road
Default

Quote:
Originally Posted by xor View Post
I will try once more.
You are amazing. .....Thanks!!!
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
COUNTIF with multiple criteria Marcia Excel 5 02-17-2019 08:16 AM
Use countif across multiple tabs for a column marconexcel Excel 6 02-21-2017 05:38 AM
Sumif with multiple sum range Haha88 Excel 6 10-29-2015 03:26 AM
multiple criteria and within a range? hhppyy Excel 1 07-17-2015 08:17 AM
Using range object to work with multiple columns kjworduser Word VBA 1 11-01-2013 03:03 AM


All times are GMT -7. The time now is 09:24 AM.


Powered by vBulletin® Version 3.8.1
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
SEO by vBSEO ©2011, Crawlability, Inc.
MSOfficeForums.com is not affiliated with Microsoft