Google Sheets: COUNTIFS and SUMIF formulas returning 0

Hi everyone!

I'm trying to create a formula that will count +1 if two different criteria is met. This is returning as "0" in 2 different scenarios:

--SCENARIO 1: COUNTIFS--

In detail, what I need the formula to do is this:

• This property belong to "Rita" (from Row6 in "Propiedades" tab)
• It is considered "Terreno" (from Row9 in "Propiedades" Tab)
• If both conditions are met, count +1

The formula that I'm using is =COUNTIFS(Propriedades!6:6,"Rita",Propriedades!9:9,"Terreno") and you can find it in the "Overview" tab on D4.

---------------------------------------------------------------------------------------------

--SCENARIO 2: SUMIF--

Similarly, I would like to sum the total price of all properties that belong to Rita:

• Property taken from Row6 in "Propiedades" tab
• Total price are the 4 big green numbers found in Row15 in "Propriedades" tab

The formula that I'm using is =sumif(Propriedades!6:6,"Rita",Propriedades!15:15), and you can find it in the "Overview" tab on G4

I'd love to understand what the right formulas should be, and what is causing the error so I can learn what to do (or not do) next time.

I'm primarily working on this on a Google Sheet: however, I posted in the formulas & functions section as the formulas in this case for COUNTIFS and SUMIF are identical between Excel or Sheets, so I attached a .XLSX file; I'm not sure if the nice formatting I worked hard on creating will stick on the XLSX version, but that should be irrelevant for this exercise.

Thanks for your time!

Welcome to the forum.

You have merged cells, so the cells you are trying to match are not in the same column - get rid of the merged cells first.

If the merged cells are non-negotiable, I won't help (sorry, that's my New Year's resolution).

Good advice regarding merged cells (which are Satan's spawn): https://excel.solutions/2016/10/usin...ctively-excel/

PS Just noticed this is for Google Sheets, so I can't help, anyway, as I don't use it. Thread moved and title updated.

Hi there,

I agree with Ali, merged cells may be good for visual presentation, but can be a problem for formulas.

If you must have the merged cells, try for cell D4 :

Formula:

Please Login or Register  to view this content.

The premis is that you must have at least 1 'Rita', then count all the 'Terreno's' in row 9 = 4
...or do you expect the result to be 1 as only 1 Terreno is in the Rita box ..?

With the un-merged sheet, make sure you have 'Rita' and 'Torreno' in the same column. then your formula in D4 will return 1.

Glad to hear your question is ansered.
Please mark this thread as 'Solved' from the Thread Tools at the top.

AH! You solved it now, even when merged included! I did not realize that in order for the formula to work, that both values would need to not only be part of the same row, but also both constraints should be on the same column. That's not very intuitive, as the formula I'm looking for only requires X values so I'm not sure why the Y values need to be aligned, but alas, this is fixed and I get to keep the formatting as well.

If you start to think this statement through a bit, it shouldn't take you long to realise why there needs to be an intersection for Excel to be able to find the answer. Feel free to ask again if you still don't get it, but with respect, what you are expecting Excel to be able to do is not very logical, and Excel needs logic. For what it's worth, I do not agree that the way Excel does work in this instance is not intuitive: it makes perfect sense to me. Let us know if you need further enlightenment.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.