Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 09-02-2021, 02:08 AM
ganesang ganesang is offline Excel function sumif help Windows XP Excel function sumif help Office 2016
Competent Performer
Excel function sumif help
 
Join Date: Jul 2018
Posts: 171
ganesang is on a distinguished road
Default Excel function sumif help

Hi Guys



I have the workbook which need to populate sumifs from sheet 2 to sheet 1.

I have used the function SUMIFS but some of the lookups only matches partially and cannot get values.

Please find the attached sheet and help me

thanks
Ganesan. g
Attached Files
File Type: xlsx Excel function help_sumif.xlsx (10.3 KB, 7 views)
Reply With Quote
  #2  
Old 09-02-2021, 04:13 AM
ArviLaanemets ArviLaanemets is offline Excel function sumif help Windows 8 Excel function sumif help Office 2016
Expert
 
Join Date: May 2017
Posts: 869
ArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud of
Default

How about this?
Sorry! Wrong file!
Attached Files
File Type: xlsx Maximum number in a series.xlsx (12.1 KB, 6 views)
Reply With Quote
  #3  
Old 09-02-2021, 04:26 AM
ArviLaanemets ArviLaanemets is offline Excel function sumif help Windows 8 Excel function sumif help Office 2016
Expert
 
Join Date: May 2017
Posts: 869
ArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud of
Default

I think it's a right one now!
Attached Files
File Type: xlsx Excel function help_sumif.xlsx (11.0 KB, 9 views)
Reply With Quote
  #4  
Old 09-02-2021, 04:30 AM
ganesang ganesang is offline Excel function sumif help Windows XP Excel function sumif help Office 2016
Competent Performer
Excel function sumif help
 
Join Date: Jul 2018
Posts: 171
ganesang is on a distinguished road
Default

Thanks it works with support column!
Reply With Quote
  #5  
Old 09-02-2021, 05:33 AM
ganesang ganesang is offline Excel function sumif help Windows XP Excel function sumif help Office 2016
Competent Performer
Excel function sumif help
 
Join Date: Jul 2018
Posts: 171
ganesang is on a distinguished road
Default

Sorry again that number 1127 may be come in any length like 987 etc

What will do in this case?
Reply With Quote
  #6  
Old 09-02-2021, 06:38 AM
ArviLaanemets ArviLaanemets is offline Excel function sumif help Windows 8 Excel function sumif help Office 2016
Expert
 
Join Date: May 2017
Posts: 869
ArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud of
Default

Quote:
Originally Posted by ganesang View Post
Sorry again that number 1127 may be come in any length like 987 etc

What will do in this case?
In case such entries, which contain this short number, are always grouped together, see attached!

In case some of such numbers must be grouped, and some not - I think you'll have to calculate all of it manually!
Attached Files
File Type: xlsx Excel function help_sumif.xlsx (13.6 KB, 5 views)
Reply With Quote
  #7  
Old 09-03-2021, 12:08 AM
ganesang ganesang is offline Excel function sumif help Windows XP Excel function sumif help Office 2016
Competent Performer
Excel function sumif help
 
Join Date: Jul 2018
Posts: 171
ganesang is on a distinguished road
Default

Hi thanks for the replay!!

I think the left function use to sort out my issue

LEFT(D116,FIND("#",SUBSTITUTE(D116,":","#",2))-1)
Reply With Quote
  #8  
Old 09-04-2021, 01:34 PM
p45cal's Avatar
p45cal p45cal is offline Excel function sumif help Windows 10 Excel function sumif help Office 2019
Expert
 
Join Date: Apr 2014
Posts: 863
p45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant future
Default

Try in B1:
Code:
=SUMIF(Sheet2!$A$1:$A$12,LEFT(A1,FIND(":",A1,4)-1) & "*",Sheet2!$B$1:$B$12)
but your:
Code:
=SUMIF(Sheet2!$A$1:$A$12,LEFT(A1,FIND("#",SUBSTITUTE(A1,":","#",2))-1) & "*",Sheet2!$B$1:$B$12)
would be more robust.
Reply With Quote
  #9  
Old 09-05-2021, 08:53 PM
ganesang ganesang is offline Excel function sumif help Windows XP Excel function sumif help Office 2016
Competent Performer
Excel function sumif help
 
Join Date: Jul 2018
Posts: 171
ganesang is on a distinguished road
Default

Thanks for reply!!
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Excel function help ganesang Excel Programming 4 09-20-2021 01:48 AM
Data connection problem excel/sharepoint using an SUMIF array mwestra74 Excel Programming 10 11-28-2017 06:26 PM
Excel function sumif help Ms Excel 2016 IF function IFnoob Excel 1 08-13-2016 11:34 PM
Excel function sumif help Excel function issue Nobody Excel 1 06-30-2014 12:26 PM
Excel function sumif help Excel Function co-lay Excel 3 02-13-2012 03:46 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 11:12 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