Results 1 to 7 of 7

Thread: Calculate a total (countifs)

  1. #1
    Seeker vonryan's Avatar
    Join Date
    Feb 2021
    Posts
    10
    Articles
    0
    Excel Version
    365

    Calculate a total (countifs)



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Dear All,

    I am trying to calculate some totals without using VBA as the spreadsheet is xlsx and I cannot change it.

    I would like to perform the following calculation:

    if Cell A is = "SP" or cell A = "DP" and Cell B = "X" and any Cell in Range (D:F) <> "" then sum of cells Range A:A.

    As an explanation if the text value in cell A is either SP or DP and the value of cell B is equal to X and there is a value (any value) in range D:F then count the number of rows in range A:A where the criteria is met.

    I have tried for a few hours but I always get Value error and I do not know why.

    In anticipation of a solution

    Vonryan

  2. #2
    Seeker RET's Avatar
    Join Date
    Nov 2020
    Location
    Spain
    Posts
    18
    Articles
    0
    Excel Version
    2019
    Let's assum that:
    - then count the number of rows in range A:A is range A1:A8
    -
    Cell A is = "SP" or cell A = "DP", is cell C1
    -
    Cell B = "X" is cell D1
    -
    any Cell in Range (D:F) <> "" is range E1:E3

    then you can use:

    =IF(AND(OR(C1="SP";C1="DP");D1="X";SUMPRODUCT(--((E1:E3)<>"")));SUM(A1:A8);"")

    I suposse that translation of Excel functions to english is OK. Sorry, but i use spanish language. Also i think that in english you should use , instead of ;
    =SI(Y(O(C1="SP";C1="DP");D1="X";SUMAPRODUCTO(--((E1:E3)<>"")));SUMA(A1:A8);"")

  3. #3
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,952
    Articles
    0
    Excel Version
    365
    try:
    Code:
    =SUM(((A2:A19="SP")+(A2:A19="DP"))*(((""<>(D2:D19))+(""<>(E2:E19))+(""<>(F2:F19)))>0)*(B2:B19="X"))
    if that returns an error, change SUM to SUMPRODUCT.
    Also, this formula looks only at rows 2 to 19, to change what rows it looks at change the 6 instances where 2 & 19 feature in the formula to your row numbers.
    Last edited by p45cal; 2021-02-28 at 06:06 PM.

  4. #4
    Seeker vonryan's Avatar
    Join Date
    Feb 2021
    Posts
    10
    Articles
    0
    Excel Version
    365
    Dear Both,

    I really appreciate your efforts in helping me resolve this problem.

    I managed to resolve it my self by using the code below.

    =SUM(COUNTIFS('CE PCOE PTSA'!M:M,{"SP","DP"},'CE PCOE PTSA'!AN:AN,"X",'CE PCOE PTSA'!AX:AX,"X") + COUNTIFS('CE PCOE PTSA'!M:M,{"SP","DP"},'CE PCOE PTSA'!AN:AN,"X",'CE PCOE PTSA'!AY:AY,"X") + COUNTIFS('CE PCOE PTSA'!M:M,{"SP","DP"},'CE PCOE PTSA'!AN:AN,"X",'CE PCOE PTSA'!AZ:AZ,"X") + COUNTIFS('CE PCOE PTSA'!M:M,{"SP","DP"},'CE PCOE PTSA'!AN:AN,"X",'CE PCOE PTSA'!BA:BA,"X"))

    However this is really ugly code and I would still like your suggestions on how to improve it.

    Moreover, in the code above you can see ('CE PCOE PTSA') which is the sheet name where I get the data. As I have many many sheets, I would like to know if I can indirectly reference a cell on the main sheet with the appropriate sheet name in a defined cell.

    Once again, I will try to adjust the support code from you both to minimise the code in each cell.

    Thank you very much

    Vonryan

  5. #5
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,952
    Articles
    0
    Excel Version
    365
    A guess:
    Code:
    =SUM((('CE PCOE PTSA'!M1:M19="SP")+('CE PCOE PTSA'!M1:M19="DP"))*("X"=('CE PCOE PTSA'!AN1:AN19))*(("X"='CE PCOE PTSA'!AX1:AX19)+("X"='CE PCOE PTSA'!AY1:AY19)+("X"='CE PCOE PTSA'!AZ1:AZ19)+('CE PCOE PTSA'!BA1:BA19="X")>0))
    SUMPRODUCT if you get an error.
    Whole column refs are not a good idea.
    Last edited by p45cal; 2021-02-28 at 11:12 PM.

  6. #6
    Seeker vonryan's Avatar
    Join Date
    Feb 2021
    Posts
    10
    Articles
    0
    Excel Version
    365
    Dear p45cal,

    I know the use use of whole column references is not a good idea, but I have many sheets that have a different number or rows. I could do this in VBA for Excel if the spreadsheet could be converted to XLSM, but it cannot because of corporate policies of Macro Enabled Spreadsheets.

    I must stick to cell formulas.

    I will try your formula because it looks a lot better than mine and I will advise you later on the results.

    Once again, I really appreciate all your assistance.

    Best Regards

    Vonryan

  7. #7
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,952
    Articles
    0
    Excel Version
    365
    Quote Originally Posted by vonryan View Post
    I know the use use of whole column references is not a good idea, but I have many sheets that have a different number or rows.
    What is the absolute maximum number of rows you'll ever possibly have to deal with, even in your wildest dreams?
    More than a mllion?
    Cutting it down to 500000 will reduce overhead significantly - I suspect it's fewer than 10000? which would be a massive reduction of overhead.

Tags for this Thread

Posting Permissions

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