Skip to main content
Solved

Sum column data when another column meets certain criteria

  • 22 July 2024
  • 8 replies
  • 63 views

Hi there - attempting essentially the same formula as described here

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

{{=SUM(IF(@"FY Plan" = "2026" @"Opex FC" 0))}}

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

Comments

Humas1985
Rank
Userlevel 6
Badge +14

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-4 || Top Rank Holder in Lucid Learning Campus
Richard U
Userlevel 4
Badge +7

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 U
Userlevel 4
Badge +7

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
Badge +1

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 U
Userlevel 4
Badge +7

 @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
Badge +1

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
Badge +1

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 U
Userlevel 4
Badge +7

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.

 

Reply


Lucid Software Inc. Supplemental Community Terms