#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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, |
#3
|
|||
|
|||
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.
|
#4
|
||||
|
||||
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. |
#5
|
|||
|
|||
But "'XXX" will never be equal with "XXX"
|
#6
|
|||
|
|||
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. |
#7
|
|||
|
|||
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. |
#8
|
||||
|
||||
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? |
#9
|
|||
|
|||
Quote:
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! |
#10
|
|||
|
|||
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.
|
#11
|
||||
|
||||
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. |
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 choose a "List" for certain "Heading" from "Modify" tool? | Jamal NUMAN | Word | 2 | 07-03-2011 03:11 AM |