Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 03-29-2019, 05:19 PM
Greggey1 Greggey1 is offline Countif multiple same range Windows Vista Countif multiple same range Office 2016
Novice
Countif multiple same range
 
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 it’s possible.
Reply With Quote
  #2  
Old 03-29-2019, 06:45 PM
Marcia's Avatar
Marcia Marcia is offline Countif multiple same range Windows 7 32bit Countif multiple same range Office 2007
Expert
 
Join Date: May 2018
Location: Philippines
Posts: 526
Marcia has a spectacular aura aboutMarcia has a spectacular aura aboutMarcia has a spectacular aura about
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 Countif multiple same range Windows Vista Countif multiple same range Office 2016
Novice
Countif multiple same range
 
Join Date: Mar 2019
Posts: 10
Greggey1 is on a distinguished road
Default

Sorry it's hard for me to explain I’m 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 Countif multiple same range Windows 10 Countif multiple same range Office 2016
Expert
 
Join Date: Oct 2015
Posts: 1,097
xor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to all
Default

Maybe like the attached.
Attached Files
File Type: xlsx Multiple_Same.xlsx (10.2 KB, 13 views)
Reply With Quote
  #5  
Old 03-30-2019, 02:48 AM
bosco_yip bosco_yip is offline Countif multiple same range Windows 10 Countif multiple same range 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 Countif multiple same range Windows Vista Countif multiple same range Office 2016
Novice
Countif multiple same range
 
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 Countif multiple same range Windows 10 Countif multiple same range Office 2016
Expert
 
Join Date: Oct 2015
Posts: 1,097
xor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to all
Default

Formula is in cell D14.
Reply With Quote
  #8  
Old 03-30-2019, 05:14 AM
Greggey1 Greggey1 is offline Countif multiple same range Windows Vista Countif multiple same range Office 2016
Novice
Countif multiple same range
 
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 Countif multiple same range Windows Vista Countif multiple same range Office 2016
Novice
Countif multiple same range
 
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 Countif multiple same range Windows Vista Countif multiple same range Office 2016
Novice
Countif multiple same range
 
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 Countif multiple same range Windows 10 Countif multiple same range Office 2016
Expert
 
Join Date: Oct 2015
Posts: 1,097
xor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to all
Default

Maybe this one is better?
Attached Files
File Type: xlsx Multiple_Same_2.xlsx (10.2 KB, 8 views)
Reply With Quote
  #12  
Old 03-30-2019, 07:29 AM
Greggey1 Greggey1 is offline Countif multiple same range Windows Vista Countif multiple same range Office 2016
Novice
Countif multiple same range
 
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 Countif multiple same range Windows 10 Countif multiple same range 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 Countif multiple same range Windows 10 Countif multiple same range Office 2016
Expert
 
Join Date: Oct 2015
Posts: 1,097
xor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to all
Default

I will try once more.
Attached Files
File Type: xlsx Multiple_Same_4.xlsx (11.0 KB, 9 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 Countif multiple same range Windows Vista Countif multiple same range Office 2016
Novice
Countif multiple same range
 
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 multiple same range COUNTIF with multiple criteria Marcia Excel 5 02-17-2019 08:16 AM
Countif multiple same range Use countif across multiple tabs for a column marconexcel Excel 6 02-21-2017 05:38 AM
Countif multiple same range Sumif with multiple sum range Haha88 Excel 6 10-29-2015 03:26 AM
Countif multiple same range multiple criteria and within a range? hhppyy Excel 1 07-17-2015 08:17 AM
Countif multiple same range Using range object to work with multiple columns kjworduser Word VBA 1 11-01-2013 03:03 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 07:50 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft