Results 1 to 30 of 30

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

Threaded View

jj2105 Need formula to count table... 05-25-2022, 09:27 AM
jj2105 Re: Need formula to count... 05-25-2022, 09:35 AM
jj2105 Need formula to count table... 05-26-2022, 02:37 AM
Glenn Kennedy Re: how to find values in a... 05-26-2022, 02:49 AM
jj2105 Re: how to find values in a... 05-26-2022, 03:11 AM
AliGW Re: Need formula to count... 05-26-2022, 02:56 AM
jj2105 Re: Need formula to count... 05-26-2022, 03:00 AM
jj2105 Re: Need formula to count... 05-26-2022, 03:05 AM
AliGW Re: Need formula to count... 05-26-2022, 03:01 AM
jj2105 Re: Need formula to count... 05-26-2022, 03:21 AM
ardross Re: Need formula to count... 05-26-2022, 03:16 AM
jj2105 Re: Need formula to count... 05-26-2022, 03:27 AM
AliGW Re: Need formula to count... 05-26-2022, 03:22 AM
AliGW Re: Need formula to count... 05-26-2022, 03:29 AM
jj2105 Re: Need formula to count... 05-26-2022, 03:42 AM
jj2105 Re: Need formula to count... 05-26-2022, 03:55 AM
jj2105 Re: Need formula to count... 05-26-2022, 04:03 AM
AliGW Re: Need formula to count... 05-26-2022, 03:44 AM
AliGW Re: Need formula to count... 05-26-2022, 04:19 AM
jj2105 Re: Need formula to count... 05-26-2022, 04:56 AM
AliGW Re: Need formula to count... 05-26-2022, 04:56 AM
jj2105 Re: Need formula to count... 05-27-2022, 03:42 AM
AliGW Re: Need formula to count... 05-27-2022, 03:43 AM
jj2105 Re: Need formula to count... 05-27-2022, 03:49 AM
AliGW Re: Need formula to count... 05-27-2022, 03:55 AM
jj2105 Re: Need formula to count... 05-27-2022, 04:15 AM
AliGW Re: Need formula to count... 05-27-2022, 04:18 AM
jj2105 Re: Need formula to count... 05-27-2022, 04:26 AM
AliGW Re: Need formula to count... 05-27-2022, 04:48 AM
jj2105 Re: Need formula to count... 05-27-2022, 04:49 AM
  1. #1
    Registered User
    Join Date
    05-25-2022
    Location
    adelaide
    MS-Off Ver
    2016
    Posts
    34

    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!
    Attached Files Attached Files
    Last edited by AliGW; 05-26-2022 at 03:24 AM. Reason: Mod note added.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 1
    Last Post: 07-28-2020, 10:30 AM
  2. Replies: 16
    Last Post: 01-25-2018, 12:18 PM
  3. Populate a table depending on dropdown data selected
    By ftrindade in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 04-25-2017, 07:08 AM
  4. [SOLVED] table(A) as an array using all rows but only selected columns to compare with table(B)
    By se3unlock in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 11-27-2015, 10:15 AM
  5. Replies: 3
    Last Post: 03-12-2014, 09:07 PM
  6. Replies: 1
    Last Post: 07-05-2013, 08:40 PM
  7. Replies: 5
    Last Post: 06-22-2010, 03:17 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1