Google Docs: Combining countifs

[glenb2]

I'm trying to combine several countifs, but I haven't gotten it to work.

Long version example
=countifS('Scope - Sprint 03 P4.R1'!$D$2:$D,A26,'Scope - Sprint 03 P4.R1'!$S$2:$S,$A$9)+countifS('Scope - Sprint 03 P4.R1'!$D$2:$D,A26,'Scope - Sprint 03 P4.R1'!$S$2:$S,$A$10)+countifS('Scope - Sprint 03 P4.R1'!$D$2:$D,A26,'Scope - Sprint 03 P4.R1'!$S$2:$S,$A$11)+countifS('Scope - Sprint 03 P4.R1'!$D$2:$D,A26,'Scope - Sprint 03 P4.R1'!$S$2:$S,$A$12)+countifS('Scope - Sprint 03 P4.R1'!$D$2:$D,A26,'Scope - Sprint 03 P4.R1'!$S$2:$S,$A$13)+countifS('Scope - Sprint 03 P4.R1'!$D$2:$D,A26,'Scope - Sprint 03 P4.R1'!$S$2:$S,$A$20)

Short version example
=sum(countifS('Scope - Sprint 03 P4.R1'!$D$2:$D,$A$25,'Scope - Sprint 03 P4.R1'!$S$2:$S,{$A$12;$A$10}))

The problem is that only the first criterion of the OR I'm trying to do is being evaluated. Could someone help so that this expression will work like -- countif $A$25 AND ($A$12 OR $A$10)

 

[glenb2]

Re: Combining countifs

Forgot to mention that I'm trying to do this in Google Docs

Thanks for any advice anyone has

 

[Jonmo1]

Re: Combining countifs

You won't be able to do that, in Excel OR Google Docs.

you can't have cell references in a {constant array}
It may work if you use sumproduct instead of sum.
However, those criteria ranges are not contiguous (A10 or A12, but not A11)
So that wouldn't work either.

I'm afraid you're probably stuck with the countifs+countifs+countifs method.

 

[glenb2]

Re: Combining countifs

You won't be able to do that, in Excel OR Google Docs.

you can't have cell references in a {constant array}
It may work if you use sumproduct instead of sum.
However, those criteria ranges are not contiguous (A10 or A12, but not A11)
So that wouldn't work either.

I'm afraid you're probably stuck with the countifs+countifs+countifs method.

Thanks, but here's the thing. I'm not getting an error and a value is being returned. The issue is that it is only returning the first OR value

I tried replacing the cell references with actual values, and the expression below returned the number of $A$25 AND EIP but not $A$25 AND WIP also.

=sum(countifS('Scope - Sprint 03 P4.R1'!$D$2:$D,$A$25,'Scope - Sprint 03 P4.R1'!$S$2:$S,{"EIP","WIP"}))


Tried wrapping inside arrayformula as well to no avail


The countifs+countifs+countifs works but is unmanageable. I'll try anything that makes this more elegant and easier to maintain. I'm not stuck on sum(countifs) if there is another way. I'm just new at this

 

[Aladin Akyurek]

Substitute 'Scope - Sprint 03 P4.R1' for X in what follows...

=SUMPRODUCT(COUNTIFS(X!$D:$D,A26,X!$S:$S,CHOOSE({1,2,3,4,5,6},$A$9,$A$10,$A$11,$A$12,$A$13,$A$20)))

 

[Jonmo1]

Re: Combining countifs

That's not really even a valid excel range reference
$D$2:$D and $S$2:$S

We need to include the Ending Row #
$D$2:$D$20 and $S$2:$S$20

This works for me in Excel.
=sum(countifS('Scope - Sprint 03 P4.R1'!$D$2:$D,$A$25,'Scope - Sprint 03 P4.R1'!$S$2:$S,{"EIP","WIP"}))

Counts rows where column D = A25 AND column S = EIP or WIP

 

[glenb2]

Substitute 'Scope - Sprint 03 P4.R1' for X in what follows...

=SUMPRODUCT(COUNTIFS(X!$D:$D,A26,X!$S:$S,CHOOSE({1,2,3,4,5,6},$A$9,$A$10,$A$11,$A$12,$A$13,$A$20)))

Tried this. Google Docs is only returning what I would expect from A9. If I swap the positions of A9 and A10 it only gives me what I would expect from A10 alone. Seems like it is similarly only grabbing the first

 

[Aladin Akyurek]

Tried this. Google Docs is only returning what I would expect from A9. If I swap the positions of A9 and A10 it only gives me what I would expect from A10 alone. Seems like it is similarly only grabbing the first

It seems COUNTIFS of the spreadsheet software you are using does not admit a set of multiple values as criteria. CHOOSE does not admit a multiple value index either.

Given all this, try the following:

=arrayformula(sum(if(X!D:D=A26,IF(isnumber(match(X!S:S,$A$9:$A$13,0))+(X!S:S=$A$20)>0,1))))