Need formula to count table range depending on conditions in selected table columns

^{MOD NOTE: Query has been amended - see post #3.}

I have a worksheet for a game that logs a fighters stats, specifically Hit Points, What style of fight, the attack and defence, result etc.

It has a sheet named "Fighter1" with a table that all the data goes into, and an overview page. The overview sheet will give me data based on the style of fight (aggressive, Defensive, Accurate, Balanced).

I want these formulas on the Overview sheet to link to the "Fighter1" sheet dynamically so that when I add a fighter, I can copy and paste the "Fighter1" sheet and overview and all my formulas reference the new sheet the same way.
I have done this through the indirect function, and naming my Sheet and the table within that sheet "Fighter1" and this is referenced back to the overview page at A1, where A1= Fighter1.

I have been able to insert a formula for the Won:Lost column in the overview that has worked:

=(COUNTIFS(INDIRECT($A$1&"[Result]"),"Won",INDIRECT($A$1&"[Style]"),B2))&":"&(COUNTIFS(INDIRECT($A$1&"[Result]"),"Lost",INDIRECT($A$1&"[Style]"),B2))

This references the table in sheet "Fighter1" and tells me for each fighting style how many wins vs losses (in this case for Aggressive)

I have then tried to create a formula for "basic hits" that has given me the #VALUE error:

=IF(AND(INDIRECT($A$1&"[Style]")=B2,INDIRECT(A1&"[Attack Defense]"="Attack")),COUNT(INDIRECT(A1&"[[1]:[13]]"),"N/A"))

This aims to give me how many times the fighter hit his opponent, (in this case in the "Aggressive" style, and in attack only).

I have tried for ages to change the formula but cant work it out, I think I may be writing the logic function incorrectly with the indirect function?
Any Help would be greatly appreciated!

One way:

=SUMPRODUCT((Fighter1[Style]=$A3)*(Fighter1[Attack Defense]="Attack")*TEXT(Fighter1[[1]:[13]],"General;-General;0;\0"))

Please see the forum rules about duplicating queries.

Having checked the workbooks provided in both threads, I have concluded that today's thread was indeed a duplicate, therfore |I have merged it with the original thread.

Well, the result table shown was identical, as was the lookup table. The only difference was that they are on the same worksheet in the closed thread.

Continue here, please - feel free to update the title of the opening post, if you wish.

I will add a note to the opening post and you can amedn the title, as I suggested.

Please provide the extra information requested in post #10.

Try this:

=SUMPRODUCT((Fighter1[Style]=A3)*(Fighter1[[1]:[13]]<>"")*(Fighter1[[1]:[13]]<>"MISS"))

and this:

=SUMPRODUCT((Fighter1[Style]=A3)*(Fighter1[[1]:[13]]="MISS"))

Great, thankyou this is what I was after

if I wanted to have this worksheet as a standard template for entering data, and have a separate overview sheet, How would I write this formula so it dynamically links to the fighter sheet. (so I can continually add New fighters and extra overview for those fighters)? I have added an example:

Glad to have helped.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, if you have not already done so, you may not be aware that you can thank those who have helped you by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.

First, change the Fighter2 table name from Fighter14 to Fighter2 in Name Manager.

Then:

=SUMPRODUCT((INDIRECT(A2&"[Style]")=B3)*((INDIRECT(A2&"[[1]:[13]]")<>"")*((INDIRECT(A2&"[[1]:[13]]")<>"MISS"))))

for Fighter1 and:

=SUMPRODUCT((INDIRECT(A8&"[Style]")=B10)*((INDIRECT(A8&"[[1]:[13]]")<>"")*((INDIRECT(A8&"[[1]:[13]]")<>"MISS"))))

for Fighter2.

Thankyou, looks like I was on the right track, I had just missed some parenthesis.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Administrative Note:

Please don't quote whole posts, especially when you are responding to the one immediately preceding your own - it's just clutter. It's OK to quote if you are responding to a post out of sequence, but limit quoted content to a few relevant lines that makes clear to whom and what you are responding. Thanks!

For normal conversational replies, try using the QUICK REPLY box below or the REPLY button instead of REPLY WITH QUOTE.

Please provide me with a sample workbook and I will happily take a look.

Nothing there with SP:

AliGW on MS365 Beta Channel (Windows 11) 64 bit

	E	F	G	H	I	J	K	L	M	N	O	P	Q
9	1	2	3	4	5	6	7	8	9	10	11	12	13
10	140	100	113	81	99	106	MISS	146
11	-144	-180	MISS	-116	-185	-151	-181	-147
12	177	85	141	137	138	47	182
13	-66	-293	-89	-286	MISS	MISS	-110
14	118	88	MISS	143	83	178	127	MISS	90	60	107
15	-124	-99	-142	-58	-110	-106	-85	-11	-187	MISS	-107
16	121	144	198	85	114	107	31	182	98
17	-71	MISS	-99	-129	-100	-73	MISS	-230
18

Sheet: Fighter1

Based on the last workbook you shared, and the one I shared back, you could try this:

=SUMPRODUCT((INDIRECT($A$2&"[Style]")=$B3)*((INDIRECT($A$2&"[[1]:[13]]")<>"")*(ISNUMBER((INDIRECT($A$2&"[[1]:[13]]"))))))

No, that's correct, so try this instead:

=SUMPRODUCT((INDIRECT(A2&"[Style]")=B3)*((INDIRECT(A2&"[[1]:[13]]")<>"")*((INDIRECT(A2&"[[1]:[13]]")<>"MISS")*((LEFT(INDIRECT(A2&"[[1]:[13]]"),2)<>"SP")))))

No except to say that I have had similar issues with it in the past.

Glad to have helped.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.