IF array formula almost works.

**Baalorian** · 03-01-2015, 06:45 PM

I've been able to create a CSE array formula that allows me to add up individual values listed to the right of specific words in a range.

Here is an example I'm making up off the top of my head:

Let's say I assign these categories numeric costs, say for a game. So, if I were to "purchase" certain types of things for a simulation:

There are three tiers, each one a subset of the lower tier, and each one assigned an absolute "cost". So

First tier costs 1 point
Second tier costs 2 points (plus the 1 point for the lower tier)
Third tier costs 3 points (plus the 3 points for the lower two tiers)
Costs are cumulative.

You'd only have to pay once for each uniquely named tier.

For example

Collectibles

A1: Treasure (tier 1) B1: Precious Metals (tier 2) C1: Gold (tier 3) [6 points]
A2: Treasure (tier 1) B2: Precious Metals (tier 2) C2: Silver (tier 3) [+ 3 points. Note 2nd tier is the same.)
A3: Treasure (tier 1) B3: Art (tier 2) C3: Tapestry (Tier 3) [+ 5 more points. Note that 2nd tier is different, increasing cost)

Total Point Cost: 14

I've created a formula that searches for each unique instance of the word in the 1st tier, and then adds up the point value of the 2nd and 3rd tiers along the same row. The user may enter whatever word they'd like for each tier, leading to a lot of random possibilities and combinations.

My formula works right now...except for one critical flaw. I can't figure out how to get it to EXCLUDE the second instance of any unique word at any of the three tiers. In other words, my sheet would calculate the above as 18. The named cells are not predetermined. But allowing the user to enter any word desired (it's for a game), means I've reached the limits of my ability to design this sheet.

Here's an example of the formula that only accounts for the unique name in the 1st tier. Imagine I'd add the cost of each tier to columns D,E, and F:

{=SUM(IF(A1:A3="Treasure",D1:D3+E1:E3+F1:F3))}

Now how on earth do I exclude the second or subsequent instances of the 2nd and 3rd tiers from the formula without going into VBA? Am I going to run into an endless loop of if-then?

**FDibbins** · 03-01-2015, 07:23 PM

Hi, welcome to the forum

To best describe or illustrate your problem you would be better off attaching a dummy workbook. The workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

**Baalorian** · 03-01-2015, 09:29 PM

Ok, thank you. I have attached the file. It is a character generator for a tabletop role-playing game, but I used more mundane categories to describe what I'm trying to do in my first post. Here is the sheet I'm really working on.

Stuff to ignore: The CP columns work the way I want and are just point costs.

The part I'm having difficulty with it calculating the "Defense Pools" part of the sheet toward the bottom. If an Ability row (one of the rows up top) is just one row, it works great. If it is two rows (i.e one Ability multiple branches), that's where it is broken, because I don't want to count duplicate tiers twice.

Ignore the whole "restricted" versus "unrestricted" stuff because that relates to the character build costs.