Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 10-08-2019, 11:09 AM
Sheri Sheri is offline SumIF with non-adjacent cells Windows 10 SumIF with non-adjacent cells Office 2016
Novice
SumIF with non-adjacent cells
 
Join Date: Oct 2019
Posts: 2
Sheri is on a distinguished road
Default SumIF with non-adjacent cells

Hello and thanks for the help. I attached a picture of my spreadsheet. I hope it helps.



I am tracking hours for 3 different projects in Excel using the codes IT, HR, and B. In column P I want to count the hours that have been assigned to the IT Project, In column R, count the hours for HR and in column T, count the B hours. Columns v through AG will collect the hours and assign the project code.

In columns V and W I will sign in and sign out, column X will calculate the number of hours for V&W, and in column Y I will assign the code of IT, HR, or B to the hours.

In columns Z and AA I will sign in and sign out, column AB will calculate the number of hours for Z&AA, and in column AC I will assign the code of IT, HR, or B to the hours.

In columns AD and AE I will sign in and sign out, column AF will calculate the number of hours for AD&AE, and in column AG I will assign the code of IT, HR, or B to the hours.

I need help figuring out how to create a formula in column P to add only the IT hours found in columns X, AB and AF based on the codes in Y, AC and AG; then in column R add only the HR hours found in columns X, AB and AF based on the codes in Y, AC and AG; then in column T add only the B hours found in columns X, AB and F based on the codes in Y, AC and AG.

SUMIF is not playing well with multiple cells, it returns an error of too many arguments. And when I try to enclose multiple cells in ( ) the #VALUE! error appears.

Of course the calculations in P, R and T will be the same syntax with a variation of the cells used. I'd appreciate an example for either column P, R or T.

Thank you so much.
Attached Images
File Type: png excel example.png (143.9 KB, 23 views)
Reply With Quote
  #2  
Old 10-09-2019, 04:27 AM
ArviLaanemets ArviLaanemets is offline SumIF with non-adjacent cells Windows 8 SumIF with non-adjacent cells 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

Attached is an example how I would do this.


The worksheet Hidden is meant to be hidden. And columns colored yellow too. (On report sheets I did hide them, but on timetable sheet I left them visible currently.)


I assumed all registered time periods will be on same date. In case time periods do extend over midnight, you have to use datetime format instead of time format for TimeIn and TimeOut, to decide how you'll calculate date for entries, and to have the date calculated instead of to be entered (or to drop date column at all and to have month/year start and end dates taken into account when calculating TimeSpent for report sheets).
Attached Files
File Type: xlsx TimetableExample.xlsx (23.6 KB, 9 views)
Reply With Quote
  #3  
Old 10-09-2019, 10:36 AM
Sheri Sheri is offline SumIF with non-adjacent cells Windows 10 SumIF with non-adjacent cells Office 2016
Novice
SumIF with non-adjacent cells
 
Join Date: Oct 2019
Posts: 2
Sheri is on a distinguished road
Default

Yes, you assumed correctly about all in one day. Sorry I didn't mention that. THANK YOU for putting so much time into this for me. I had no idea it would be so involved! Too bad the SUMIF won't work on nonadjacent cells, that would be much easier! Thanks again! Sheri
Reply With Quote
  #4  
Old 10-09-2019, 10:51 PM
ArviLaanemets ArviLaanemets is offline SumIF with non-adjacent cells Windows 8 SumIF with non-adjacent cells 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

The main reason for different approach was, that with way you did it, whenever you have to add a new project, you had to design the workbook anew. With my approach, you add a new project into projects table, and it's all you need to do! I myself prefer to design my applications at once, and then let them work for years without updating the design.

Also my approach is not sensitive to order of entries in table. You can sort it in any way you like.

And you can add any new report sheets you'll need in future. Btw. having different entries project-wise on same row makes creating reports over certain period practically impossible, unless there will always be an entry (and only one) for every project per day.

Instead of SUMIF(), my advice is to use SUMIFS().
And having all entries on separate sheet designed like a database table makes calculations easier really. With your design you need formulas like = SUMIF(...) + SUMIF(...) + ... - a SUMIF() for every project! Imagine you have 20 - 30 different projects!
Reply With Quote
  #5  
Old 10-17-2019, 09:00 PM
kvsrinivasamurthy kvsrinivasamurthy is offline SumIF with non-adjacent cells Windows XP SumIF with non-adjacent cells Office 2007
Novice
 
Join Date: Oct 2017
Posts: 2
kvsrinivasamurthy is on a distinguished road
Default

Try this. In P17 then copy to other cells.

=SUMIF($Y17:$AG17,Q17,$X17:$AF17)
Reply With Quote
Reply

Tags
sumif

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Borders on merged cells also appearing on unwanted adjacent cells MrsC Word Tables 2 08-19-2019 09:14 AM
SumIF with non-adjacent cells Need help with an IF formula, which is based on adjacent cells being in the Top 30% of a range FarStar Excel 17 07-26-2017 07:55 AM
Conditional formating all cells in an array based on adjacent cells deejay Excel 6 12-20-2016 12:00 PM
SumIF with non-adjacent cells Adding adjacent cells and having the sum appear in two separate places Seamus Sean Excel 4 12-11-2016 10:34 PM
Using alternating borders in adjacent cells jrevard Word 0 10-15-2015 09:07 AM

Other Forums: Access Forums

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