Using an exsisting formula to create a new formula

Otman

Hi,

I have this initial formula which is giving me a number of tests done by the matrice Urine) in a Smartsheet for year 2020 :

=IF(SUMIFS([Nombre de résultats Réussie (R)-EA-Référence]:[Nombre de résultats Réussie (R)-EA-Référence]; [Code de l'analyse - EA RÉFÉRENCE]:[Code de l'analyse - EA RÉFÉRENCE]; [Code-Portée]@row; [Matrice - EA - Référence]:[Matrice - EA - Référence]; "Urine"; [EA réalisés l'année passé-EA-Référence]:[EA réalisés l'année passé-EA-Référence]; "OK"; [R-RÉférence]:[R-RÉférence]; "R") >= 1; SUMIFS([Nombre de résultats Réussie (R)-EA-Référence]:[Nombre de résultats Réussie (R)-EA-Référence]; [Code de l'analyse - EA RÉFÉRENCE]:[Code de l'analyse - EA RÉFÉRENCE]; [Code-Portée]@row; [Matrice - EA - Référence]:[Matrice - EA - Référence]; "Urine"; [EA réalisés l'année passé-EA-Référence]:[EA réalisés l'année passé-EA-Référence]; "OK"; [R-RÉférence]:[R-RÉférence]; "R"))

I have similar sheets for 2021 and 2020, all are separate sheets

Know I am trying to create a formula in a master sheet to know how much the total tests of that matrice have been done for years 2022, 2021 and 2022, I used this formula to start

 

=SUMIF ({code-ref-2020}; [Code]@row; {Matrice-urine})

This formula is giving the right number for 2022; however when I tried to make it a formula column is giving invalid reference

And when I add the others years is giving me the same message invalid reference

 

+ SUMIF({code-ref-2020}; [Code]@row; {Matrice-urine})+ SUMIF({code-ref-2020}; [Code]@row; {Matrice-urine})

 

Could you please help on that, it is possible to use an existing calculated value trough a formula in another formula.

 

Thank you in advance for your help

Genevieve P.

Hi @Otman

Invalid Reference means that there's something going on with one of your cross-sheet references:

{code-ref-2020} or {Matrice-urine}

Can you double check that they are looking at the correct columns? In the one SUMIF statement they would both need to be looking at the same sheet (e.g you can't have one looking at Sheet 1 and a different reference to Sheet 2).

Then when you're looking to add them together, you would need to delete the {reference} and create an entirely new one. This means your SUMIF() + SUMIF() should have different references if they're looking at different sheets.

For example:

=SUMIF({code-ref-2020 Sheet 1}; [Code]@row; {Matrice-urine Sheet 1})+ SUMIF({code-ref-2020 Sheet 2}; [Code]@row; {Matrice-urine Sheet 2}) + SUMIF({code-ref-2020 Sheet 3}; [Code]@row; {Matrice-urine Sheet 3})

This would then have 6 different columns referenced, 2 for each sheet. They would need to have different names. See: View, modify, or delete cross sheet references in formulas

Let me know if this helps explain your issue!

Cheers,

Genevieve

Otman

Hi Genevieve,

Tahnk you for your answer, i did chek the formula, and i find that both are looking to the same columns as you mentionned,

have a look to this formula :

=SUMIFS({Test1-Urine 1 AN}; {EA/Année-Test 1.1 Plage 2}; [Code-Portée]@row) + SUMIFS({Test2-uRINE 1 AN}; {EA/Année-Test 1.2 Plage 1}; [Code-Portée]@row)

a referencing any SUMIFS TO a different sheet (Test1) for THE FIRST sumif and sheet test 2 for the second sumifs, they are giving me the righ results in calculation however, when i try to apply they as a culumns reference i get the Invalid ref.

Am geting the palge criteria ([Code-Portée]@row) by using a formula (Index(match) from another sheet, can this be the issue when i tried to apply the formula as a culumn formula

Thank you for your help

Genevieve P.

Hi @Otman

There is a possibility that the error is coming from [Code-Portée]@row, yes. If any of the cells in the ranges you have selected have an error, then this will come into your SUMIF formula as well.

Can you try wrapping an IFERROR around the INDEX(MATCH to see if that helps?

=IFERROR(INDEX(...MATCH(...)), "")

Otman

Hi Genevieve,

I did put a IFERROR FORMULA, BUT Is not working, i am wondering if the plage that am trying to use to caculate in the formula SUMif cames already from another formula, this is why is not working, Can we have a teams confrence call to discuss it. Thank you for your help

Genevieve P.

Hi @Otman

There are a couple possibilities why it may not be working, but you should be able to reference a cell with a formula as the matching item to look for in your SUMIF statement.

You could test to see if this is the case by creating a helper Text/Number column and then manually type in the data from your [Code-Portée]@row cell. If your SUMIF works when looking at your typed version, then there is an issue with how your INDEX(MATCH is being seen.

I would suggest contacting Smartsheet Support with a full screen recording of your issue to explain where all of the different ranges are looking at. They'll be able to troubleshoot with you via a private channel where you can share this information.

Cheers,

Genevieve