+ Reply to Thread
Results 1 to 9 of 9

Google Sheets: COUNTIFS and SUMIF formulas returning 0

  1. #1
    Registered User
    Join Date
    01-23-2021
    Location
    Dublin
    MS-Off Ver
    2020
    Posts
    4

    Question 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!
    Attached Files Attached Files
    Last edited by AliGW; 01-23-2021 at 02:45 AM.

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider (Windows 11 64-bit)
    Posts
    52,108

    Re: COUNTIFS and SUMIF formulas returning 0

    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.
    Last edited by AliGW; 01-23-2021 at 02:51 AM.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  3. #3
    Registered User
    Join Date
    01-23-2021
    Location
    Dublin
    MS-Off Ver
    2020
    Posts
    4

    Re: COUNTIFS and SUMIF formulas returning 0

    Hi Ali,

    First of, thank you for the welcoming and for giving me clever insights already.

    I don't think I can unmerge the entire spreadsheet (as I can't even remember all of the things I've merged there), however, I have fully unmerged every cell that would be needed for this exercise (basically everything that the formula would touch, which would be rows 6, 9 and 15 of the "Propiedades" tab)

    Would that be enough?

    Thanks in advance (and I'll take on your no merging rationale on my future spreadsheets!)

    Alvaro
    Attached Files Attached Files

  4. #4
    Valued Forum Contributor
    Join Date
    03-24-2020
    Location
    Thailand
    MS-Off Ver
    Office 2016
    Posts
    519

    Re: Google Sheets: COUNTIFS and SUMIF formulas returning 0

    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: copy to clipboard
    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 ..?
    If your Question is answered; please mark it SOLVED. If you are happy with a member's solution, say 'Thanks' and click the 'Star' to Add Reputation.

  5. #5
    Valued Forum Contributor
    Join Date
    03-24-2020
    Location
    Thailand
    MS-Off Ver
    Office 2016
    Posts
    519

    Re: COUNTIFS and SUMIF formulas returning 0

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

  6. #6
    Registered User
    Join Date
    01-23-2021
    Location
    Dublin
    MS-Off Ver
    2020
    Posts
    4

    Re: Google Sheets: COUNTIFS and SUMIF formulas returning 0

    Hi ORoos,

    Noted about the merging, unfortunately this is done for a client and she specifically requested that sort of visual presentation (names are made up but will change once she adds her own details).

    Yes, my expected result on SCENARIO 1 would be to be 1, as only once is the condition met (both being a "terreno" and belonging to "rita). The idea is that as the client expands into more properties, this number will automatically change.

  7. #7
    Registered User
    Join Date
    01-23-2021
    Location
    Dublin
    MS-Off Ver
    2020
    Posts
    4

    Re: COUNTIFS and SUMIF formulas returning 0

    Quote Originally Posted by ORoos View Post
    With the un-merged sheet, make sure you have 'Rita' and 'Torreno' in the same column. then your formula in D4 will return 1.
    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.

    Regardless, I see how complex formulas can be broken when using merging, so I'll try to abstain from it when possible.

    Thanks a lot!
    -A

  8. #8
    Valued Forum Contributor
    Join Date
    03-24-2020
    Location
    Thailand
    MS-Off Ver
    Office 2016
    Posts
    519

    Re: Google Sheets: COUNTIFS and SUMIF formulas returning 0

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

  9. #9
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider (Windows 11 64-bit)
    Posts
    52,108

    Re: Google Sheets: COUNTIFS and SUMIF formulas returning 0

    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Countifs not returning value on >=
    By shuddle in forum Excel General
    Replies: 3
    Last Post: 08-31-2020, 05:15 PM
  2. [SOLVED] Countifs no returning the correct value
    By lizsantiago07 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-13-2017, 11:08 PM
  3. Countifs returning Zero
    By MikeSham in forum Excel General
    Replies: 5
    Last Post: 07-20-2016, 05:53 PM
  4. [SOLVED] Countifs returning wrong value
    By mangeshp4 in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 07-12-2016, 08:30 AM
  5. [SOLVED] CountIfs returning 0
    By jsmilke in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 03-19-2015, 04:59 PM
  6. countifs returning value error
    By ammartino44 in forum Excel General
    Replies: 2
    Last Post: 10-03-2014, 06:22 PM
  7. [SOLVED] COUNTIFS returning #VALUE!
    By photoryan in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 12-18-2013, 01:07 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1