Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 04-07-2022, 05:11 AM
croberts croberts is offline How to sum EstCost if Job = "100000"? Windows 10 How to sum EstCost if Job = "100000"? Office 2019
Novice
How to sum EstCost if Job = "100000"?
 
Join Date: Apr 2022
Posts: 12
croberts is on a distinguished road
Default How to sum EstCost if Job = "100000"?

Hi,
We actually have Office 365 but this was not an option in the registration screen. I'm on Windows 10.

I have some raw data in a spreadsheet, sheet is named "JobCost", that has these columns:
Job, EstCost, ActCost, EstHours, ActHours

The Job column is actually a text field and is formatted that way in Excel. This is actually fake data from a database and the job number is a text field in the database as well.

There are multiple entries/rows for each job on this sheet "JobCost". Like this:

Job Job part Start date EstHours ActHours EstCost ActCost
100000 01 1/1/2021 1.2
100001 01 1/2/2021 2.3
100001 01 1/3/2021 1.2 35
100001 01 1/4/2021 1.3 40
100001 01 1/5/2021 35
100002 01 1/6/2021 30
100001 01 1/7/2021 5.5
100001 01 1/8/2021 1.2

On another sheet called "SumByJob" I have these columns, but I want one row per job to sum all EstHours from the sheet "JobCost":


Job, EstHours, ActHours, EstCost, ActCost.

I want to sum all EstHours from the JobCost sheet, for one job and put it on the sheet "SumByJob" under the EstHours column. I've looked at the VLOOKUP functions and that doesn't look right. I've looked at SUMIF and it is not working for me.

I'm not used to doing this sort of thing in Excel. Can someone help me out please? I have attached my Excel file for you to look at.

Thank you!

EDIT: Results should be this:

Job EstHours ActHours EstCost ActCost
100000 1.2
100001 7.8 3.7 35 75
100002 30
Attached Files
File Type: xlsx EstActSS.xlsx (12.6 KB, 10 views)
Reply With Quote
  #2  
Old 04-07-2022, 11:12 PM
ArviLaanemets ArviLaanemets is offline How to sum EstCost if Job = "100000"? Windows 8 How to sum EstCost if Job = "100000"? 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

1. On sheet Jobcost you had job codes formatted as text, and then entered them as numbers - getting numeric strings. On sheet SumByJob you had job codes formatted as General, and then entered them preceded with apostrophe to prevent them handled as numbers. "SomeText" doesn't equal with "'SomeText"! So I formatted column A of SumByJob as text, and removed apostrophes from there. After that, formulas SUMIF and SUMIFS (I prefer latter as more universal one) will work.

2. A better solution is to create a pivot table instead. No need to enter new jobs manually. Simply set Pivot to be refreshed on open in Pivot Table Properties, and when there is need to refresh Pivot through session, simply right-click on any cell of Pivot table ans select Refresh from drop-down menu,
Attached Files
File Type: xlsx EstActSS.xlsx (20.0 KB, 5 views)
Reply With Quote
  #3  
Old 04-11-2022, 03:02 AM
croberts croberts is offline How to sum EstCost if Job = "100000"? Windows 10 How to sum EstCost if Job = "100000"? Office 2019
Novice
How to sum EstCost if Job = "100000"?
 
Join Date: Apr 2022
Posts: 12
croberts is on a distinguished road
Default

Oh thank you. So the data types in Excel must be more strongly typed than I thought. I'm used to working with Perl where the differences between a number and string are of no consequence.
Reply With Quote
  #4  
Old 04-11-2022, 03:42 AM
Debaser's Avatar
Debaser Debaser is offline How to sum EstCost if Job = "100000"? Windows 7 64bit How to sum EstCost if Job = "100000"? Office 2010 32bit
Competent Performer
 
Join Date: Oct 2015
Location: UK
Posts: 221
Debaser will become famous soon enough
Default

FWIW, SUMIF/SUMIFS don't care whether the data is numeric text or a real number, so:
=SUMIF(Jobcost!$A:$A,$A2,Jobcost!D:D)
copied across and down would do the job.
Reply With Quote
  #5  
Old 04-11-2022, 03:54 AM
ArviLaanemets ArviLaanemets is offline How to sum EstCost if Job = "100000"? Windows 8 How to sum EstCost if Job = "100000"? 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 Debaser View Post
FWIW, SUMIF/SUMIFS don't care whether the data is numeric text or a real number, so:
=SUMIF(Jobcost!$A:$A,$A2,Jobcost!D)
copied across and down would do the job.
But "'XXX" will never be equal with "XXX"
Reply With Quote
  #6  
Old 04-11-2022, 03:56 AM
croberts croberts is offline How to sum EstCost if Job = "100000"? Windows 10 How to sum EstCost if Job = "100000"? Office 2019
Novice
How to sum EstCost if Job = "100000"?
 
Join Date: Apr 2022
Posts: 12
croberts is on a distinguished road
Default

Ok so I added some more records to the sheet Jobcost so the "Job" number is out of order. This is how our database actually works. This sample data is supposed to represent our database. I have a pivot table on the tab named "PivotTable". I get 2 entries on the pivot table for job 100001. And the forum deleted my images I pasted in.


Do I have to sort the Job column on JobCost sheet before I do a pivot table?


I've attached an updated spreadsheet.



Thank you!


EDIT: Spreadsheet updated here.
Attached Files
File Type: xlsx EstActSS.xlsx (22.1 KB, 6 views)
Reply With Quote
  #7  
Old 04-11-2022, 04:11 AM
croberts croberts is offline How to sum EstCost if Job = "100000"? Windows 10 How to sum EstCost if Job = "100000"? Office 2019
Novice
How to sum EstCost if Job = "100000"?
 
Join Date: Apr 2022
Posts: 12
croberts is on a distinguished road
Default

Well I see a problem. On sheet Jobcost, cells A8 and A9, I cannot use the F2 edit to remove the single quote before the job number, when I remove the single quote, and hit ENTER, the single quote reappears. And when I type over the job (already formatted as text), the single quote gets put back in there by Excel.


Ok I had to retype in rows 8 and 9, make sure the job number was formatted as text (it was) then delete rows 8-9. That's the only way I could get rid of the single quote before those 2 jobs. Then I refreshed the pivot table and it worked.



So a number prefixed with a single quote, is not the same as a number formatted as text.




Thanks for clearing up this data type thing.
Reply With Quote
  #8  
Old 04-11-2022, 04:36 AM
Debaser's Avatar
Debaser Debaser is offline How to sum EstCost if Job = "100000"? Windows 7 64bit How to sum EstCost if Job = "100000"? Office 2010 32bit
Competent Performer
 
Join Date: Oct 2015
Location: UK
Posts: 221
Debaser will become famous soon enough
Default

Quote:
Originally Posted by ArviLaanemets View Post
But "'XXX" will never be equal with "XXX"
Really? Works just fine for me. All the relevant figures are included in the SUMIF results. I get 7.8 for EstHours for 100001 even though one row in the source data is 100001 and the other is '100001

Are you saying it doesn't work for you?
Reply With Quote
  #9  
Old 04-11-2022, 06:08 AM
ArviLaanemets ArviLaanemets is offline How to sum EstCost if Job = "100000"? Windows 8 How to sum EstCost if Job = "100000"? 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 croberts View Post
Well I see a problem. On sheet Jobcost, cells A8 and A9, I cannot use the F2 edit to remove the single quote before the job number, when I remove the single quote, and hit ENTER, the single quote reappears. And when I type over the job (already formatted as text), the single quote gets put back in there by Excel.
It's really weird behavior! I was able to correct those cells by:
a) Copying the value in cell over with one behaving normally from upper rows;
b) Copying an upper normally behaving cell, and then PasteSpecial Formats for those 2 cells.

(And I checked Data Validation - there was none defined for those cells)

Looks like a bug!
Reply With Quote
  #10  
Old 04-11-2022, 06:18 AM
croberts croberts is offline How to sum EstCost if Job = "100000"? Windows 10 How to sum EstCost if Job = "100000"? Office 2019
Novice
How to sum EstCost if Job = "100000"?
 
Join Date: Apr 2022
Posts: 12
croberts is on a distinguished road
Default

Quote:
Originally Posted by Debaser View Post
Really? Works just fine for me. All the relevant figures are included in the SUMIF results. I get 7.8 for EstHours for 100001 even though one row in the source data is 100001 and the other is '100001

Are you saying it doesn't work for you?
Correct, it doesn't work for me on Office 365. In my pivot table the results for 100001 and '100001 produce two different rows. But the results from SUM look fine. (I wanted to learn to do this 2 different ways.) So it looks like a bug in the Pivot Table but not the SUM or SUMIF functions.
Reply With Quote
  #11  
Old 04-11-2022, 06:45 AM
Debaser's Avatar
Debaser Debaser is offline How to sum EstCost if Job = "100000"? Windows 7 64bit How to sum EstCost if Job = "100000"? Office 2010 32bit
Competent Performer
 
Join Date: Oct 2015
Location: UK
Posts: 221
Debaser will become famous soon enough
Default

That question wasn't actually directed at you.

It won't work in a pivot table because that does differentiate between text and numbers. SUMIF(S) and COUNTIF(S) do not.
Reply With Quote
Reply

Tags
sumif

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Mailto Hyperlink Formula contains too large of "Body" receiving "#Value" need to find workaround MCamera Excel 1 03-02-2022 07:52 PM
Excel 2003: VBA "Function" causes "#VALUE!" errors after running "insert/delete row" custom macro Matt C Excel Programming 2 01-08-2022 06:03 AM
Combine "sort within groups" and "identify duplicate/unique values" in two different variables. civilcervant Excel 3 06-12-2017 07:27 AM
remove repeated words with " macro " or " wild cards " in texts with parentheses and commas jocke321 Word VBA 2 12-10-2014 11:27 AM
How to sum EstCost if Job = "100000"? How to choose a "List" for certain "Heading" from "Modify" tool? Jamal NUMAN Word 2 07-03-2011 03:11 AM

Other Forums: Access Forums

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