Query function in google sheets

0
votes

I am struggling to find a solution to my problem.

In the "Folders" tab of my google sheet (link below), there are 3 query functions located in columns Q, R, S (in yellow). Query in column Q pulls the titles from TEST!F4:F if it states "LABOR" in TEST!AH4:AH. The other two query formulas sum up the numbers; One in TEST!N4:N and the other one in TEST!O4:O.

The problem is that the sums are not on the same row as corresponding titles. The whole data combined should look exactly like in FOLDERS!L1:N.

For example, "FRONT GRADING & DEMO" should have a number 6 next to it in column R and nothing (or 0) in column S. Another example, " POURED PAVER DRIVEWAY W/ TURF" should have a number 6 next to it in column R and 9.6 in column S.

The reason why FOLDERS!L1:N is not used is that I couldn't get it to work with 2 criteria. First criteria is that it needs to consider TEST!E4:E (that's what it currently does) and the other criteria is to not add up the number if TEST!C4:C is "TRUE" (chequebooks ticket).

I hope the above explanation is not confusing and someone could help me. Maybe there is another solution besides a query function?

SAMPLE SHEET

Thank you

1
google-sheetsgoogle-sheets-formula
Looking quickly, I see how the numbers don't line up. For the project titles (Folders!Q4), you look for 'Labor' in Test!AH. So you skip the first project in your data. But for your query in Folders!R3, you sum your costs, grouping by project, even if they have no labour. So your first sum is blank (no labor cost) and the 6 from your second project, is now on the wrong project title. I'll look at it more slowly and see if I can help come up with something. – kirkg13
To clarify the above comment, it seems like column E in Test, almost always has two "project numbers" per project, so both 1 and 2 for project "FRONT GRADING & DEMO" , with the second one needed when there is "labor" involved. – kirkg13
@kirkg13 You're first message really helped me to look at it from another angle! Thank you. I've got everything working now but there's only one issue. The number in FOLDERS!S15 should be in FOLDERS!S14 and I can't figure out why it's not there. Maybe you could help figure this out? Everything else is matching how it should do. In response to your question, yes it normally goes in pairs. One will be material and one will be labour (for the same project), hence there are 2 project numbers per project. – Vladlen445
Yes, the critereia for the two formula - one for the project name, and one for the SUM - in Q3 and S2 don't quite match. I'm digging in to it now. – kirkg13
The problem is Test!Row 158 - project #28, name = "Vines in driveway with wire trellis". This row has C=FALSE, and AI='Labor'. So it gets counted in your SUM formula as a separate row. But it does not get listed in your Names formula in Q3, since AH is blank. This is probably just a typo - should C158 be checked off as TRUE, to hide it? Also, for future maintenance, you may want to revieww a couple of your "helper" columns. Do you need/want both AH and AI - they do the same thing don't they? – kirkg13

1 Answers

2
votes

Note: If this should be entered as a comment, and not an answer, I apologise.

Vladlen445, I found a way to fix the mis-alignment issues. Like you, I created a helper column, AJ in your TEST tab. AJ is filled with the project name, onto each row. I add the project number on the end, but this is not necessary, just cosmetic.

Then I created a new FOLDERS-Demo tab to show the calculations in a different way. Instead of using Test! column E, I now use Test! column AJ, which is the project name.

The project names are listed alphabetically, then the values for SUM(N) and SUM(O). There is a sort option, in FOLDER-Demo!W1, to sort by project number (from Test! column E) but this then shows the same error as your yellow section, due to project 28 in C158.

For your original formulas, in the yellow section, I haven't changed anything. I assume turning C158 to TRUE will correct your mis-alignment there. Let me know if this has clearly answered your question.

Note that FOLDER-Demo!W1 has data validation (Proj# or ProjName) for the sort options, and FOLDER-Demo!T1 is a pointer cell to control the sort column for the queries. It can be hidden by making the ink color the same as the background, and locked/protected to prevent accidental changes.