Spreadsheet Update - Color / Rarity Needs

4. Number of cards needed by color
5. Number of cards needed by rarity

Through my counting and planning I wanted to know how many more 3200 count boxes I would need in order to keep the organization I have setup.  I had not completed playsets for each of the editions yet, but I wanted to know if the needed cards would be too much for the boxes I already have.

With this feature enhancement I can look at the Cards Needed columns, look at the Type, and sum those number counts that I needed.  Then I could gather all the needed numbers from all the edition tabs and compile them in a single spreadsheet to find the totals.  From those totals I could separate the Modern from the Legacy cards, and figure out how much space I would need to store all the cards together.

I also wanted to validate how many more Mythic Rares, Rares, Uncommons, and Commons were needed to fill out my playsets.  So in the same formulas I did the same thing but looking at the rarity.

Here is the new spreadsheet, and we are going to be looking at Column A and B with Rows 42 - 59.

W stands for White
U stands for Blue
B stands for Black
R stands for Red
G stands for Green
D stands for Gold
C stands for Colorless
A stands for Artifact
L stands for Land



M stands for Mythic Rare
R stands for Rare
U stands for Uncommon
C stands for Common
L stands for Land
T stands for Token



Cell A42: =SUMIF(W2:W602,"White",Z2:Z602)
Cell A43: =SUMIF(W2:W602,"Blue",Z2:Z602)
Cell A44: =SUMIF(W2:W602,"Black",Z2:Z602)
Cell A45: =SUMIF(W2:W602,"Red",Z2:Z602)
Cell A46: =SUMIF(W2:W602,"Green",Z2:Z602)
Cell A47: =SUMIF(W2:W602,"Gold",Z2:Z602)
Cell A48: =SUMIF(W2:W602,"Colorless",Z2:Z602)
Cell A49: =SUMIF(W2:W602,"Artifact",Z2:Z602)
Cell A50: =SUMIF(W2:W602,"Land",Z2:Z602)

This should look into the W column and check the color of the card, and then SUM the number of cards needed from the Z column.  So for each category I am able to see how many of each color I need. And since we are sorting by Vintage/Modern/Standard, then by color this will allow me to total up all the counts for each of the Vintage/Modern/Standard sets to see how much space I need when filling up the boxes I have.

Cell A55: =SUMIF(X2:X602,"M",Z2:Z602)
Cell A56: =SUMIF(X2:X602,"R",Z2:Z602)
Cell A57: =SUMIF(X2:X602,"U",Z2:Z602)
Cell A58: =SUMIF(X2:X602,"C",Z2:Z602)
Cell A59: =SUMIF(X2:X602,"L",Z2:Z602)
Cell A60: =SUMIF(X2:X602,"T",Z2:Z602)

This should look into the X column and check the rarity, and then SUM the number of the cards needed from the X column.  So this will basically let me know what I need to concentrate on when trading.  I have my need lists in Evernote, but as I am trying to concentrate on Modern blocks and Vintage sets I want to make sure that I look at the sets that I need rares in.

So for instance, Worldwake I only need Mythic Rares, so I am not as worried about that set as I am the other sets where I need more of the rares.  The issue was that I just had a total number of cards that I needed per set, but I really didn't know what that meant.  So not I can see that Worldwake total at 296 and out of that number I really only care about 17 Mythics, and 5 Rares.

Or I can see my Mercadian Masques total is set to 64 more cards needed, but with this new enhancement I know that 58 of those needed are basic lands.

You will be able to see more from the Total and Collection tabs when I get there.

**EDIT**

I needed to add some Cell configurations and decided to edit this blog to incorporate the changes.  Just in case someone wants to follow along and build their own spreadsheet.  This way they will not need to go back and edit all their Set Tabs with this new information.

Cell A65: =SUMIF(X2:X600,"M",AA2:AA600)
Cell A66: =SUMIF(X2:X600,"R",AA2:AA600)
Cell A67: =SUMIF(X2:X600,"U",AA2:AA600)
Cell A68: =SUMIF(X2:X600,"C",AA2:AA600)
Cell A69: =SUMIF(X2:X600,"L",AA2:AA600)
Cell A70: =SUMIF(X2:X600,"T",AA2:AA600)


This will gather price totals for all the cards that still need to be bought, and separate those totals out by rarity.