Formula help with SUMIFS. Would like to incorporate an IFMAX in there somehow.

Here's the headers in one sheet and a few example rows to picture what I'm looking to do.

So project 8 for example might see his engagement go up from Facture (invoice) 9.6 to 9.7. The engagement of PO ending in 1258 went from 888888 to 999999. That's a scenario that might happen which I'm trying to capture in a formula.

On another sheet I groupe all POs in one line and I would like to add only the MAX instance of Engagement columns of PO ending with 1258 for the case of projet 8.

Here's the formula I have so far.

I am basically trying to replace the =1 to a =MAX for the same other conditions.

{TEST SDC RANGE 4} is my Engagement column

{SDC Rev0 Range 2} is my Projet column which I reference in the formula above.

{TEST SDC RANGE 3} is my Is Distinct? column, which I'm trying to add only the values of engagement for which Is Distinct is the highest.


I hope that's clear enough and I appreciate all your help.

Thank you.

Answers

  • AravindGP
    AravindGP ✭✭✭✭✭✭

    Hi,


    It looks like you're trying to get the max value rather than summing up the values in your Engagement column. If my understanding of the scenario is correct, the formula you could use would be =INDEX(COLLECT({TEST SDC RANGE 4}, {SDC Rev0 Range 2}, Projet@row, {TEST SDC RANGE 3}, MAX({TEST SDC RANGE 3})), 1)

    Thanks,

    Aravind GP| Principal Consultant

    Atturra Data & Integration

    M: +61493337445

    E:Aravind.GP@atturra.com

    W: www.atturra.com

  • Thanks AravindGP for your time.

    A clarification if I may. I beleieve it is a sum I'm trying to do because on the 2nd sheet all POs for a single project are grouped in one line. And in the Engagement column of that 2nd sheet, I want to sum all Engagement columns of the 1st sheet only if the "Is distinct?" column is at the highest value for that PO. Does that make any sense?


    Basically a project can have many POs, and those POs might see their value go up in time. So I want to add the latest value of engagement of each PO for every project. Please let me know if this helps. I appreciateit very much!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!