Microsoft Office Forums SumIF with non-adjacent cells
 Register FAQ Search Today's Posts Mark Forums Read

#1
10-08-2019, 11:09 AM
 Sheri Windows 10 Office 2016 Novice Join Date: Oct 2019 Posts: 2

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
 excel example.png (143.9 KB, 18 views)
#2
10-09-2019, 04:27 AM
 ArviLaanemets Windows 8 Office 2016 Expert Join Date: May 2017 Posts: 508

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
 TimetableExample.xlsx (23.6 KB, 4 views)
#3
10-09-2019, 10:36 AM
 Sheri Windows 10 Office 2016 Novice Join Date: Oct 2019 Posts: 2

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
#4
10-09-2019, 10:51 PM
 ArviLaanemets Windows 8 Office 2016 Expert Join Date: May 2017 Posts: 508

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.

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!
#5
10-17-2019, 09:00 PM
 kvsrinivasamurthy Windows XP Office 2007 Novice Join Date: Oct 2017 Posts: 2

Try this. In P17 then copy to other cells.

=SUMIF(\$Y17:\$AG17,Q17,\$X17:\$AF17)

 Tags sumif

 Thread Tools Display Modes Linear Mode

 Similar Threads Thread Thread Starter Forum Replies Last Post MrsC Word Tables 2 08-19-2019 09:14 AM FarStar Excel 17 07-26-2017 07:55 AM deejay Excel 6 12-20-2016 12:00 PM Seamus Sean Excel 4 12-11-2016 10:34 PM jrevard Word 0 10-15-2015 09:07 AM

All times are GMT -7. The time now is 01:34 PM.

 -- Default Style -- Lightweight -- New Mobile Contact Us - Privacy Statement - Top