SUM & COUNTIFS Question using {"xxx","xxx"}

[CWatsonJr]

I am trying to shorten up some of my formulas and it just isn't working right... wondering if there is a rule I don't know about or if I am just not writing the formula correctly. Here are what is working and what is not working:

Generically Written:

Works:

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Doesn't Work:

Please Login or Register  to view this content.

Exactly how it is in my workbook:

Working:

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Not Working:

Please Login or Register  to view this content.

Is there a reason why a second set of criteria placed in brackets won't work with the SUM/COUNTIFS combination?

Thank you for your help!!

[XOR LX]

Hi,

Induce the second array by transposing it:

=SUM(COUNTIFS('[CAData.xlsx]CurrYR Crimes'!$G:$G,{"1971","1972","1974","1981","1982","1983","1984","1985"},'[CAData.xlsx]CurrYR Crimes'!$I:$I,">"&$C$39,'[CAData.xlsx]CurrYR Crimes'!$I:$I,"<"&$D$39,'[CAData.xlsx]CurrYR Crimes'!$D:$D,TRANSPOSE({"THEFT","GTP"})))

Or, equivalently:

=SUM(COUNTIFS('[CAData.xlsx]CurrYR Crimes'!$G:$G,{"1971","1972","1974","1981","1982","1983","1984","1985"},'[CAData.xlsx]CurrYR Crimes'!$I:$I,">"&$C$39,'[CAData.xlsx]CurrYR Crimes'!$I:$I,"<"&$D$39,'[CAData.xlsx]CurrYR Crimes'!$D:$D,{"THEFT";"GTP"}))

Regards

[FDibbins]

Not really sure what you are trying to do here, but why not leave out the countifS() part and just use sumifS()?

Maybe something thing like this?

=SUMIFS(sum-range,'[CAData.xlsx]CurrYR Crimes'!$G:$G,">=1971",'[CAData.xlsx]CurrYR Crimes'!$G:$G,"<=1985", '[CAData.xlsx]CurrYR Crimes'!$I:$I, ">="&$C$39, '[CAData.xlsx]CurrYR Crimes'!$I:$I, "<="&$D$39, '[CAData.xlsx]CurrYR Crimes'!$D:$D, "THEFT"))

(not quite sure which is your sum range?)

[CWatsonJr]

FD - I am using the SUM statement so I can use an array in the COUNTIFS. This way I am not using multiple COUNTIFS with a + between them to gather my data.

XOR - The TRANSPOSE statement didn't work, even if I use Ctrl-Shift-Enter so the whole thing becomes an array formula. Using a semi-colon instead of a comma didn't work either. The "THEFT" and "GTP" are text occurrences that I am counting.

[bebo021999]

CWatsonJr, Unfortunately, the format COUNTIFS with array does not support second array inside. For second array, use another COUNTIFS, I believe.

[XOR LX]

Works absolutely fine for me, and doesn't require array-entry.

@bebo Not true. The SUMIF(S)/COUNTIF(S) family operate on multi-dimensional arrays, providing they are coerced appropriately.

Regards

[CWatsonJr]

It was me... I went back and looked more carefully at the statement and I left out a couple of quotation marks! It works fine now!!

Thank you very much!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

[XOR LX]

You're welcome.

[SoerenK]

First it is not easy to get behind what you want to achieve.
Second it is generally not polite to answer a question with a question.

However, are you really sure that you first formula does achieve what you want it to achieve?
Sumif and Countif can only accept one condition at a time.
By that matter everything beyond criteria1.1 should be ignored in the formula you showed.
At least that is the case when I tried it in 2010.
But it should not have worked in 2007 either. 2003 I am no longer sure.

Please first check whether that is the case for you as well.

Matrix formulas are the only way known to me to consider multiple conditions in a single go:
{sum(range1 * (range2 = criteria range1) * (range2 >= criteria range1) * etc } for AND conditions
Even there you need to use boundaries as in >= or repeat each specific condition in a seperate bracket:
{sum (range1 * ((range2 = "theft") + (range2 = "GTP"))>0))} would do an OR condition,
i.e. would sum over the rows in range1 where the same row in range2 is either "theft" or "GTP".

Hope this helps.

[CWatsonJr]

@SoerenK - I am using COUNTIFS and not COUNTIF And yes - the formulas are doing exactly what I am asking of them. Thank you - sorry if you felt I was being impolite

[XOR LX]

@SoerenK

"Sumif and Countif can only accept one condition at a time"

I realise that you are new to the forum, but please be careful with making statements such as these to people seeking solutions, especially since this particular one is simply not true.

Take the following simplified version, for example:

=SUM(COUNTIFS(D1:D4,{"a","b"},E1:E4,{"c","d"}))

Providing there is EITHER an "a" in the D column and a "c" in the corresponding row in the E column OR a "b" in the D column and a "d" in the corresponding row in the E column, then this row will be counted.

Furthermore, transpose either of these arrays (but not both), i.e.:

=SUM(COUNTIFS(D1:D4,{"a";"b"},E1:E4,{"c","d"})) or

=SUM(COUNTIFS(D1:D4,{"a","b"},E1:E4,{"c";"d"}))

(or equivalents using TRANSPOSE())

and this time any row for which there is EITHER an "a" OR "b" in the D column and EITHER a "c" OR "d" in the corresponding row in the E column will be counted.

Regards