Can you use sum and min together

bhil

Hello,

Im trying to get a min of 4 if PA points and SS points are both active then have 4 or the number that is either ss or pa add to the other columns, is that possible? Here's the formula I came up with but its not working.

=IFERROR(IF(OR([Status Detail]@row = "Ready to return", [Status Detail]@row = "Transfer"), "N/A", MIN(4, IF([PA Points ]@row, 4, 0) + (IF([SS Points ]@row, 4, 0) + SUM([Ed Points ]@row, [Visit Points ]@row, [Wait Points]@row,[PA Points ]@row, [SS Points ]@row),0, "N/A"))))

Gillian C

Hi @bhil

I think you need this formula?

=IF(OR([Status Detail]@row = "Ready to return", [Status Detail]@row = "Transfer"), "N/A", (IF(AND([SS Points]@row <> "", [PA Points]@row <> ""), 4, IF(AND([SS Points]@row <> "", [PA Points]@row = ""), [SS Points]@row, IF(AND([SS Points]@row = "", [PA Points]@row <> ""), [PA Points]@row, 0))) + SUM([Visit Points]@row, [ED Points]@row, [Wait Points]@row)))

Based on what you said above my sheet looks like this:

To summarize if you have either 'Ready to return' or 'Transfer' as the Status Detail the formula will return N/A…regardless of the values in the other columns.

If both SS Points and PA Points are populated then the sum of these columns will be 4 regardless of the values, and if only SS or PA is populated it will have that value, what ever the value (4 or either of the SS or PA column values) this is added to the sum of the remaining columns (Visit Points, ED Points and Wait Points).

Hope this helps.

Gillian C

Hi @bhil

I've been looking at your formula and don't understand a couple of sections.

1 Could you tell me the reason for the IF statements highlighted in bold below? (If you want the minimum value to be 4 I don't understand why you need to change another value to 0 if it is 4?…which is how I'm interpreting your formula)

2 And also what is the purpose of the 0 and N/A in bold? If you are using the Min function all the values need to be numerical in nature.

=IFERROR(IF(OR([Status Detail]@row = "Ready to return", [Status Detail]@row = "Transfer"), "N/A", MIN(4, IF([PA Points ]@row, 4, 0) + (IF([SS Points ]@row, 4, 0) + SUM([Ed Points ]@row, [Visit Points ]@row, [Wait Points]@row,[PA Points ]@row, [SS Points ]@row),0, "N/A"))))

Once I know that I should be able to help more. :)

bhil

I didnt know how to apply the actual number from the other column so i tried setting it to 0 to see if that worked. So its supposed to be if "SS" and "PA" both have values the new value will be 4 and if only one is active it displays that amount. Then I would either 4 or the amount in SS or PA and add it to the other columns to get a sum.

The N/A in bold was for the IFERROR put N/A which i believe is different from the first N/A which put that if one of the values is "ready to return" or "transfer".

Gillian C

Hi @bhil

I think you need this formula?

=IF(OR([Status Detail]@row = "Ready to return", [Status Detail]@row = "Transfer"), "N/A", (IF(AND([SS Points]@row <> "", [PA Points]@row <> ""), 4, IF(AND([SS Points]@row <> "", [PA Points]@row = ""), [SS Points]@row, IF(AND([SS Points]@row = "", [PA Points]@row <> ""), [PA Points]@row, 0))) + SUM([Visit Points]@row, [ED Points]@row, [Wait Points]@row)))

Based on what you said above my sheet looks like this:

To summarize if you have either 'Ready to return' or 'Transfer' as the Status Detail the formula will return N/A…regardless of the values in the other columns.

If both SS Points and PA Points are populated then the sum of these columns will be 4 regardless of the values, and if only SS or PA is populated it will have that value, what ever the value (4 or either of the SS or PA column values) this is added to the sum of the remaining columns (Visit Points, ED Points and Wait Points).

Hope this helps.

bhil

Thank you so much you are a life saver