countifs/sumifs Issue.

[natesteiner21]

I have a worksheet that has ten columns. We'll call them A-J. I have set up a formula to count a/b, a/c, a/d, a/e and determine which ones meet the criteria that I've set up. Say it counts if there is an "x" in the rows. My issue is that I want to be able to check it for a/b, a/c, a/d, a/e, etc... and a/b/c, a/b/d, a/b/e, etc... and a/b/c/d, a/b/c/e, etc...

I've got the forumulas set up to do that but I'm doing something wrong because it is duplicating the data. i.e. if the row has an "x" in it for column a, b, c it counts it in a/b, a/c, b/c, and a/b/c. I only want it to count in the a/b/c area.

Same exact thing for f-j but using sumifs.

Any help out there? I can post the sheet if you would like.

 

[alansidman]

Post a small Excel sheet (not a picture) showing realistic & representative sample data WITHOUT confidential information (10-20 rows, not thousands...) and some manually calculated results. Please use XL2BB to post your response. Directions for its use are in my signature.

 

[natesteiner21]

Post a small Excel sheet (not a picture) showing realistic & representative sample data WITHOUT confidential information (10-20 rows, not thousands...) and some manually calculated results. Please use XL2BB to post your response. Directions for its use are in my signature.

Action Analysis_Template.xlsx
	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1	Sharp	W/L	Big Money	W/L	Pro Systems	W/L	Model	W/L	Experts	W/L	W/L
2	5	$0.00	5	$0.00	5	$0.00	4	$0.00	3	$0.00	$0.00		Sh/BM	3	0
3	x		x				x						Sh/PS	2	0
4			x		x								Sh/Mo	3	0
5	x		x		x		x		x				Sh/Ex	2	0
6							x						BM/PS	3	0
7	x		x						x				BM/Mo	2	0
8					x								BM/Ex	3	0
9	x				x								PS/Mo	1	0
10			x		x				x				PS/Ex	2	0
11	x						x						Mo/Ex	1	0
12													Sh/BM/PS	1	0
13													Sh/BM/Mo	2	0
14													Sh/BM/Ex	2	0
15													Sh/PS/Mo	1	0
16													Sh/PS/Ex	1	0
17													Sh/Mo/Ex	1	0
18													BM/PS/Mo	1	0
31

Cell Formulas
Range	Formula
G2,O2,M2,K2,I2	G2	=COUNTIF(G3:G100,"x")
P2:Q2,N2,L2,J2,H2	P2	=SUM(P3:P100)
P3:P18,N3:N18,L3:L18,J3:J18,H3:H18	P3	=IF(OR(ISBLANK($B3),ISBLANK($C3)),"",IF(AND($E3="Spread",(($B3-$C3)+$F3)>0.1,O3="x"),10,IF(AND($E3="Spread",(($B3-$C3)+$F3)<-0.1,O3="x"),-11,IF(AND($E3="Spread",(($B3-$C3)+$F3)=0,O3="x"),0,IF(AND($E3="Over",(($B3+$C3)>$F3),O3="x"),10,IF(AND($E3="Over",(($B3+$C3<$F3)),O3="x"),-11,IF(AND($E3="Over",(($B3+$C3=$F3)),O3="x"),0,IF(AND($E3="Under",(($B3+$C3)<$F3),O3="x"),10,IF(AND($E3="Under",(($B3+$C3>$F3)),O3="x"),-11,IF(AND($E3="Under",(($B3+$C3=$F3)),O3="x"),0,))))))))))
Q3:Q18	Q3	=IF(OR(ISBLANK($B3),ISBLANK($C3)),"",IF(AND($E3="Spread",(($B3-$C3)+$F3)>0.1),10,IF(AND($E3="Spread",(($B3-$C3)+$F3)<-0.1),-11,IF(AND($E3="Spread",(($B3-$C3)+$F3)=0),0,IF(AND($E3="Over",(($B3+$C3)>$F3)),10,IF(AND($E3="Over",(($B3+$C3<$F3))),-11,IF(AND($E3="Over",(($B3+$C3=$F3))),0,IF(AND($E3="Under",(($B3+$C3)<$F3)),10,IF(AND($E3="Under",(($B3+$C3>$F3))),-11,IF(AND($E3="Under",(($B3+$C3=$F3))),0,))))))))))
T2	T2	=COUNTIFS(G$2:G$100,"x",I$2:I$100,"x")
U2	U2	=SUMIFS(H$2:H$100,G$2:G$100,"x",I$2:I$100,"x")
T3	T3	=COUNTIFS(G$2:G$100,"x",K$2:K$100,"x")
U3	U3	=SUMIFS(H$2:H$100,G$2:G$100,"x",K$2:K$100,"x")
T4	T4	=COUNTIFS(G$2:G$100,"x",M$2:M$100,"x")
U4	U4	=SUMIFS(H$2:H$100,G$2:G$100,"x",M$2:M$100,"x")
T5	T5	=COUNTIFS(G$2:G$100,"x",O$2:O$100,"x")
U5	U5	=SUMIFS(H$2:H$100,G$2:G$100,"x",O$2:O$100,"x")
T6	T6	=COUNTIFS(I$2:I$100,"x",K$2:K$100,"x")
U6	U6	=SUMIFS(J$2:J$100,K$2:K$100,"x",I$2:I$100,"x")
T7	T7	=COUNTIFS(M$2:M$100,"x",I$2:I$100,"x")
U7	U7	=SUMIFS(J$2:J$100,M$2:M$100,"x",I$2:I$100,"x")
T8	T8	=COUNTIFS(O$2:O$100,"x",I$2:I$100,"x")
U8	U8	=SUMIFS(J$2:J$100,O$2:O$100,"x",I$2:I$100,"x")
T9	T9	=COUNTIFS(K$2:K$100,"x",M$2:M$100,"x")
U9	U9	=SUMIFS(L$2:L$100,K$2:K$100,"x",M$2:M$100,"x")
T10	T10	=COUNTIFS(K$2:K$100,"x",O$2:O$100,"x")
U10	U10	=SUMIFS(L$2:L$100,K$2:K$100,"x",O$2:O$100,"x")
T11	T11	=COUNTIFS(M$2:M$100,"x",O$2:O$100,"x")
U11	U11	=SUMIFS(N$2:N$100,M$2:M$100,"x",O$2:O$100,"x")
T12	T12	=COUNTIFS(G$2:G$100,"x",I$2:I$100,"x",K$2:K$100,"x")
U12	U12	=SUMIFS(H$2:H$100,G$2:G$100,"x",I$2:I$100,"x",K$2:K$100,"x")
T13	T13	=COUNTIFS(G$2:G$100,"x",I$2:I$100,"x",M$2:M$100,"x")
U13	U13	=SUMIFS(H$2:H$100,G$2:G$100,"x",I$2:I$100,"x",M$2:M$100,"x")
T14	T14	=COUNTIFS(G$2:G$100,"x",I$2:I$100,"x",O$2:O$100,"x")
U14	U14	=SUMIFS(H$2:H$100,G$2:G$100,"x",I$2:I$100,"x",O$2:O$100,"x")
T15	T15	=COUNTIFS(G$2:G$100,"x",M$2:M$100,"x",K$2:K$100,"x")
U15	U15	=SUMIFS(H$2:H$100,G$2:G$100,"x",M$2:M$100,"x",K$2:K$100,"x")
T16	T16	=COUNTIFS(G$2:G$100,"x",O$2:O$100,"x",K$2:K$100,"x")
U16	U16	=SUMIFS(H$2:H$100,G$2:G$100,"x",O$2:O$100,"x",K$2:K$100,"x")
T17	T17	=COUNTIFS(G$2:G$100,"x",M$2:M$100,"x",O$2:O$100,"x")
U17	U17	=SUMIFS(H$2:H$100,G$2:G$100,"x",M$2:M$100,"x",O$2:O$100,"x")
T18	T18	=COUNTIFS(M$2:M$100,"x",I$2:I$100,"x",K$2:K$100,"x")
U18	U18	=SUMIFS(J$2:J$100,M$2:M$100,"x",I$2:I$100,"x",K$2:K$100,"x")

 

[natesteiner21]

Hopefully you can see that I'm trying to count and sum where things match. For example I want to count Sharp and Big Money in the row (SH/BM) on the right. But I only want to count those two. Not every instance where they show up together in the main body of the worksheet. i.e. where there is an "x" in sharp/big money/pro systems I would like the column on the right to put that in the correct area (SH/BM/PS) and not duplicate it the sharp/big money row, the sharp/pro systems row, and the big money/pro systems row.

I only sent in part of the sheet, the rest of it has the area on the right go all the way down to cover every combination of the five column headers.

I'm also guessing that if there is a fix for countifs there would be a fix for sumifs. Maybe not...

I know I'm doing a terrible job explaining this and maybe i can't be helped but thought I'd check with you guys.

Thanks so much for your help. This is a really great forum.

 

[Fluff]

I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

For T2 try

=COUNTIFS(G$2:G$100,"x",I$2:I$100,"x",K$2:K$100,"<>x",M$2:M$100,"<>x",O$2:O$100,"<>x")

 

[natesteiner21]

Thanks for all of your help.

 

[Fluff]

Glad we could & thanks for the feedback.

Also thanks for updating your profile