+ Reply to Thread
Results 1 to 11 of 11

SUMIFS is retaining Zero

  1. #1
    Registered User
    Join Date
    03-11-2021
    Location
    Hyderabad
    MS-Off Ver
    2019
    Posts
    12

    SUMIFS is retaining Zero

    In the Sheet "Allocation tool", it has a column "Available hours based on project dates" which has SUMIFS formula. How ever I try it retains Zero '0'

    I am unable to understand if this is because the date lists are having another column reference or something else.

    =SUMIFS($F8:$L8:$R8:$X8:$AD8:$AJ8:$AP8:$AV8:$BB8:$BH8:$BN8:$BT8:$BZ8:$CF8:$CL8:$CR8:$CX8:$DD8:$DJ8:$DP8:$DV8:$EB8:$EH8:$EN8:$ET8:$EZ8:$FF8:$FL8:$FR8:$FX8:$GD8,$F$6:$L$6:$R$6:$X$6:$AD$6:$AJ$6:$AP$6:$AV$6:$BB$6:$BH$6:$BN$6:$BT$6:$BZ$6:$CF$6:$CL$6:$CR$6:$CX$6:$DD$6:$DJ$6:$DP$6:$DV$6:$EB$6:$EH$6:$EN$6:$ET$6:$EZ$6:$FF$6:$FL$6:$FR$6:$FX$6:$GD$6,"=>"&$G$3,$F$6:$L$6:$R$6:$X$6:$AD$6:$AJ$6:$AP$6:$AV$6:$BB$6:$BH$6:$BN$6:$BT$6:$BZ$6:$CF$6:$CL$6:$CR$6:$CX$6:$DD$6:$DJ$6:$DP$6:$DV$6:$EB$6:$EH$6:$EN$6:$ET$6:$EZ$6:$FF$6:$FL$6:$FR$6:$FX$6:$GD$6,"<="&$H$3)

    I all attach the sheet for reference
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    18,407

    Re: SUMIFS is retaining Zero

    Dates in F6 etc for "Available Hours" are TEXT not Dates no will never match G3 & H3

    Alternative formula

    =SUMPRODUCT(($F8:$GI8)*($F$7:$GI$7="Available hours")*($F$6:$GI$6>=$G$3)*($F$6:$G$I6<=$H$3))

  3. #3
    Registered User
    Join Date
    03-11-2021
    Location
    Hyderabad
    MS-Off Ver
    2019
    Posts
    12

    Re: SUMIFS is retaining Zero

    Thanks for your time, when I try to copy past exactly =SUMPRODUCT(($F8:$GI8)*($F$7:$GI$7="Available hours")*($F$6:$GI$6>=$G$3)*($F$6:$G$I6<=$H$3))

    It gives me error stating that there is issue with formula

    Also, I could not understand ($F$7:$GI$7="Available hours") since Available hours does not have values and values are only present in the column above and below it (the entire data is not in a table)

    Sorry if this is a silly question but I am using these functions for the first time. Also because I am not doing any product here
    Last edited by girishankar09; 03-11-2021 at 03:20 AM.

  4. #4
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    18,407

    Re: SUMIFS is retaining Zero

    You will get errors because the dates in row 1 of "Associate Skills" are TEXT not proper dates so they never match the dates in G3 and H3.

    the dates need to be changed to proper dates.

    ($F$7:$GI$7="Available hours") simply selects columns which have headers of "Available Hours".

    It will work but only when dates in "Associate Skills" are changed to dates. You need to put the dates in row 2 Move everything down) as Tables appear to row 1 as text headers (I don't like Tables!).

  5. #5
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    18,407

    Re: SUMIFS is retaining Zero

    I assume you simply changed to headers in "Associate Skills" from "Column 10", "column 11" etc with dates e.g 01-apr-2021. Hence these dates are simply text

    So (as far as I understand tables) you need to remove the table header row and replace it with your own so columns A:L will be as current header row and M onwards will be proper dates. Obviously this will impact on some of your formulae

  6. #6
    Registered User
    Join Date
    03-11-2021
    Location
    Hyderabad
    MS-Off Ver
    2019
    Posts
    12

    Re: SUMIFS is retaining Zero

    As I understand, you mean to say that under "Associate_Skills" sheet, I will need to have columns with dates (from M till the end) removed from table and just be range(not like a table). Then make sure they are dates by right clicking and format to dates. And Yes, it will change a lot of formulas in "Allocation tool" since I have vlookup for values which I will have to do if this is the only way. Also, I need to change columns like F6 in "Allocation tool" which has values like "=Associate_Skills!M1" and just need to right click and change to dates format. This is my understanding, correct me if I am wrong and I will try doing this

  7. #7
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    18,407

    Re: SUMIFS is retaining Zero

    Yes, unfortunately you need to have your dates as dates so any comparisons will work.

    NOTE: using SUMPRODUCT, if you need, for example, to sum "Occupied Hours" you will need the date in column G etc.

  8. #8
    Registered User
    Join Date
    03-11-2021
    Location
    Hyderabad
    MS-Off Ver
    2019
    Posts
    12

    Red face Re: SUMIFS is retaining Zero

    It actually worked, Thanks...
    Last edited by girishankar09; 03-11-2021 at 06:05 AM.

  9. #9
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    18,407

    Re: SUMIFS is retaining Zero

    Good to hear: well done!

    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.

  10. #10
    Registered User
    Join Date
    03-11-2021
    Location
    Hyderabad
    MS-Off Ver
    2019
    Posts
    12

    Re: SUMIFS is retaining Zero

    Sure, I have added the reputation

  11. #11
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    18,407

    Re: SUMIFS is retaining Zero

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

    Can you please do the above - thank you.

+ 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. retaining formula in row
    By serenitylai in forum Excel General
    Replies: 1
    Last Post: 10-10-2017, 02:59 PM
  2. Replies: 0
    Last Post: 08-22-2017, 03:49 PM
  3. retaining value
    By QuizToon in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-31-2015, 06:05 PM
  4. Splitting / Retaining
    By Deepa12 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-26-2015, 04:39 AM
  5. SUMIFS Puzzle - Trying to avoid adding multiple SUMIFS to get valid result
    By haldavid in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 01-09-2013, 03:42 PM
  6. [SOLVED] Sumifs, problem with the formula: =sumifs(c10:c200,<=today(),0)
    By Faustocruz in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 10-08-2012, 04:26 AM
  7. Retaining Focus
    By John Eakins in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-01-2007, 03:03 AM

Tags for this Thread

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