Solved

Sum column data when another column meets certain criteria

Forum|Forum|2 years ago
July 22, 2024
8 replies
151 views

Hi there - attempting essentially the same formula as described here

for a sum of finances within 1 given year, tried with;

per guidance but all I get is an ‘Unexpected end of expression’ error

Best answer by Richard Udell

Thank you @Humas1985 for your contribution and awesome attention to the Community (as always 😄).

Hi @Claree22, I would check to confirm that you’re correctly referencing the location of the properties “FY Plan” and “”Open FC”. Here’s our formulas documentation section on shape references for shape properties and this section on data references if you’re referring to a property at the document collection level (see data-linking menu on the left-hand sidebar).

Also, please note that in the example you cited, commas appear to be missing in the example formula. Please see my screenshot for the correct syntax. I’ll see to it that the original example gets updated!

Humas1985

Lucid Legend Level 10

Hi @Claree22

Custom formulas for data manipulation within the document is not possible, which is causing the "Unexpected end of expression" error.

Hope this helps - Happy to help further!!
Thank you very much and have a great one!
Warm regards

Huma || Lucid Legend

Richard UdellAnswer

Lucid support team

Thank you @Humas1985 for your contribution and awesome attention to the Community (as always 😄).

Hi @Claree22, I would check to confirm that you’re correctly referencing the location of the properties “FY Plan” and “”Open FC”. Here’s our formulas documentation section on shape references for shape properties and this section on data references if you’re referring to a property at the document collection level (see data-linking menu on the left-hand sidebar).

Also, please note that in the example you cited, commas appear to be missing in the example formula. Please see my screenshot for the correct syntax. I’ll see to it that the original example gets updated!

Richard Udell

Lucid support team

The example formula in the other post you cited has been updated to include commas! I apologize for the mix-up.

Please let me know if you have any questions.

C

Claree22Author

Hi @Richard U - thanks so much for your response! Super helpful, though I’m still not getting to the result I’d like, I’ve pasted in a screenshot of the values I’d like SUM’d as an example below;

So I would expect my formula to be able to SUM (based on my IF condition) 300 as the total Opex FC value for 2024, and 200 as the Opex FC value for 2023. Is this possible?

Richard Udell

Lucid support team

@Claree22 This should be possible! Can you please provide a screenshot(s) of the entire document (similar to the one I shared before) showing the shape that contains the formula and where the data is stored on the document? This will help me sort out how we can reference this data properly.

Alternatively, could you send a temporary Support PIN for this document? This will allow me to take a closer look at the issue you’re experiencing. For more information on generating a Support PIN, check out this Help Center article.

C

Claree22Author

Hi @Richard U pleased to hear it! Here’s my support PIN; e9hBV6zO2gcU

As you’ll see I’ve got the data in the document itself.

Let me know if you need anything more.

C

Claree22Author

Hi @Richard U - just wondering if you’ve been able to take a look at my document please? The table I need these formulas in can be found in the ‘Dashboard’ tab

Richard Udell

Lucid support team

Hi @Claree22 , thank you for your providing the document pin and for your patience with my reply! I was able to reference the values in your Capex FC column using FIELDLOOKUP.

For example: {{=FIELDLOOKUP(“Project Roadmap”, “[Insert key from reference column, “Project”]”, “[Insert column whose corresponding value you care to return, “Capex FC”]”)}}.

However, I can’t find a way to iterate through your column with FIELDLOOKUP and return the values to sum them based on an IF statement (e.g. FY2023, FY2024, etc.). I believe a more effective workaround would be to generate these sum values using an excel formulat on a new tab in your linked dataset then call those values to your Lucidchart canvas directly.

Create an account in the community

Log in to the community