Formulas: Nested if and summarize

[Ignacio_Salazar]

Hi everyone!
I am having a hard time with some formulas due type of cells and blank cells. Hope you can help me.

This is the Nested IF I wrote:

IF( Cuota=PagoEnero, “Pagado”,IF(PagoEnero=BLANK(),“No” , IF(PagoEnero=0,Cuota-PagoEnero ,IF(Cuota-PagoEnero>0, Cuota-PagoEnero))))

The formula returns the same result if the cell is blank or is =0.
I think this is because the statement will always be true in the second one but i dont know how to fix it

My second issue is that, I do the same formula multiple times (Enero, Febrero…) and i need to summarize only the results that returns a number (not text) and make a sum

For example: Enero=0, Febrero=No, Marzo= , … Returns: Enero

CONCATENATE(IF(Enero=BLANK(), “”, IF(Enero=0, “Enero”, IF(Enero>0, “Pagado”))),IF(Febrero>0,"",“Febrero”)…and so on)

I think due the column “Enero” is a formula does not recognize the result as a number, this is not working. I have tried to change text to number and it fixed but brings other problems.

Could anyone give me some advice?

Thank you so much for your answers

[Justin_Barrett · Accepted solution]

Welcome to the community, @Ignacio_Salazar! :grinning_face_with_big_eyes: First off, I made a quick translation for myself (and others who don’t speak Spanish) so I could understand more clearly what you’re trying to do:

Cuota = Fee
PagoEnero = PaymentJanuary
Pagado = Paid

Depending on the values of the {Cuota} and {PagoEnero} fields, the output from that first formula will either be a status indicator—“Pagado” or “No”—or a number representing some payment value. Formula logic aside, I believe that mixing output types like that is part of the problem you’re encountering.

From my experience, clean data modeling is important for a variety of reasons, and one key way to keep a data model clean is to be as specific as possible about what each field represents. Having a field that represents either a status or a numerical value leads to greater complexity and confusion, not greater clarity.

I strongly suggest splitting the logic of that first formula across two fields: one to contain only the status, the other to contain only the appropriate payment value. Even though it will mean adding more fields, you will likely find it much easier to write your formulas because you’re not mixing different concepts and data types in the same place.

[Ignacio_Salazar]

Thank you so much for your answer.
I did not take a number representing some payment value because I use this number to summarize payment but also to show if it was made that payment… :rofl:
I think splitting in two fields is the better way. I have tried this because I call this values to an app and is a bit difficult to make that reference from 2 fields… and also because I have not removed from my head the logic of the spreedsheets

I also understood when i saw post and videos of difference between data base and spreedsheets

PS. thanks for your translation. I made the translation in an image but I dont know why I cant show it :grinning_face_with_big_eyes:

[Ignacio_Salazar]

Just a quick question. What abaout blank cells and cells= 0? Is there an option to difference it besides COUNTA?

[Justin_Barrett]

Can you be more specific? I’m not sure what you’re asking.