Formulas for counting different criteria

[desuth]

go

go

no

no

go and go

no and no

go and no

no and go

no

no

no

no

3

4

1

2

go

go

no

no

go

go

no

go

go

no

no

go

<tbody>
</tbody>

I am trying to get the formulas for the go and go, no and no, go and no, no and go.

I'm not sure of how to get the formula to count between the different criteria.

 

[ExcelElliott]

go

go

no

no

go and go

no and no

go and no

no and go

no

no

no

no

go

go

no

no

go

go

no

go

go

no

no

go

<tbody>
</tbody>

Assuming the cell containing "go and go" is G1, with blanks in columns C and E, this is the formula I inserted to G2:

=SUM(N(CHOOSE({1,2},--($A$1:$A$5&" and "&$B$1:$B$5=G$1),--($D$1:$D$5&" and "&$E$1:$E$5=G$1))>0))

Using Control Shift Enter.

Let me know if there are any questions or concerns.

 

[RasGhul]

Hi,

This worked for me;

Book1
	A	B	C	D	E	F	G	H
1	go	go	no	no	gogo	nono	gono	nogo
2	no	no	no	no	3	4	1	2
3	go	go	no	no
4	go	go	no	go
5	go	no	no	go
Sheet1

Cell Formulas
Range	Formula
E2	=SUMPRODUCT(($A$1:$A$5&$B$1:$B$5=E$1)+($C$1:$C$5&$D$1:$D$5=E$1)*1)
F2	=SUMPRODUCT(($A$1:$A$5&$B$1:$B$5=F$1)+($C$1:$C$5&$D$1:$D$5=F$1)*1)
G2	=SUMPRODUCT(($A$1:$A$5&$B$1:$B$5=G$1)+($C$1:$C$5&$D$1:$D$5=G$1)*1)
H2	=SUMPRODUCT(($A$1:$A$5&$B$1:$B$5=H$1)+($C$1:$C$5&$D$1:$D$5=H$1)*1)

 

[ExcelElliott]

I figured out how to make my solution presentation look a little better.

	A	B	C	D	E	F	G	H	I	J	K	L	M
1	go	go		no	no		go and go		no and no		go and no		no and go
2	no	no		no	no		3		4		1		2
3	go	go		no	no		3		4		1		2
4	go	go		no	go
5	go	no		no	go

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Array Formulas Cell Formula G3 {=SUM(N(CHOOSE({1,2},--($A$1:$A$5&" and "&$B$1:$B$5=G$1),--($D$1:$D$5&" and "&$E$1:$E$5=G$1))>0))} I3 {=SUM(N(CHOOSE({1,2},--($A$1:$A$5&" and "&$B$1:$B$5=I$1),--($D$1:$D$5&" and "&$E$1:$E$5=I$1))>0))} K3 {=SUM(N(CHOOSE({1,2},--($A$1:$A$5&" and "&$B$1:$B$5=K$1),--($D$1:$D$5&" and "&$E$1:$E$5=K$1))>0))} M3 {=SUM(N(CHOOSE({1,2},--($A$1:$A$5&" and "&$B$1:$B$5=M$1),--($D$1:$D$5&" and "&$E$1:$E$5=M$1))>0))} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

 

[Aladin Akyurek]

@ RasGhul

No need for the *1 coercion as the + operator takes care of that.

 

[desuth]

Awesome works like a charm, thank yall for the help!

 

[RasGhul]

@ RasGhul

No need for the *1 coercion as the + operator takes care of that.

Argh yes thanks.