Calculate a total (countifs)

[vonryan]

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

[RET]

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);"")

[p45cal]

try:

=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.

[vonryan]

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

[p45cal]

A guess:

=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.

[vonryan]

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

[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.

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.