#1




Sumif
In cell range A3:A100, the user can choose from a dropdown that contains either A, B, C or D.
I want to sum the range using the below values. A=100 B=75 C=50 D=25 Can someone help me with this? Your help is greatly appreciated. 
#2




You have a range A3:A100, with cells in this range having string values either "A", "B", "C" or "D". And you want to get SUM(A3:A100)? It's no brainer  it will be always 0!
Or did you want something else? 
#3




I'm soooo sorry. Not sure what I was thinking when I wrote this.
So the values would be: A's=100 B's=75 C's=50 D's=25 In the A3:A100 range, I would have 25 A's, 25 B's, 25 C's and 23 D's. So all of that would equal A=2500 B=1875 C=1250 D=575  6200 What would the syntax for the formula be to yield the sum of 6200? Thank you 
#4




Does this do?

#5




PERFECT! Thank you so much!

#6




I attempted to apply your formula to my data and I was not successful. I am aware of how to name a range, but how do you incorporate the column header "values" into the formula?
Your help is greatly appreciated. 
#7




I defined tables as Tables (Insert>Table)
In defined Tables, you can use specific Table Formula syntax, where you can refer to Table using Table name, to Table column header using column header in specific way, to Table column using column header like [ColumnHeader], to cell in specific column in same row like [@ColumnHeader], etc. When you any cell in defined Table, an additional menu selection is added, where you can change Table properties, like name, size/range, etc. When you enter some text adjacent to righmost colum header in Table, the additional column is added automatically. When you insert a column into Table, the new column is implemented automatically, and a column is named automatically. You also can change Table size/range from menu selection. When you enter anything into some cell immediately below last row of Table, or press Tab when rightmost lower cell is selected, a row is added automatically into table. To add several rows at go, you have to change Table size/range. When the whole column of Table contains same formula or format, then whenever a new row is added, or formula is edited in any sell of column, this formula/format is applied to added row(s) automatically. Editing the formula in column in a way which doesn't update the formula in whole column, results in stopping of automatic formula update for this column. Whenever you edit Table name or column header in Table, all references to it are updated automatically everywhere in your workbook. Dragging whole columns into new positions in Table doesn't affect your formulas, except cases where you refer to column position numbers in your formula. (e.g. VLOOKUP(). But you can use there something like MATCH(HeaderName,TableName[#[header]],0) instead of column number  I haven't Excel available at moment and I don't remember exact syntax.) 
#8




Thank you for all of your help with this.
So, I named the range of data and then I named the table. I then copied your formula and changed formula to reflect my table and range name, but it's not working for me. I get the dreaded message "There's a problem with this formula..." I have attached a screen shot of the message. What am I doing wrong? I have no doubt it's user error. 
#9




For what you need named range?
1. Open my example file; 2. Copy A3:A17 and PasteValues e.g. to I3 and edit header to 'Literals' (or simply enter new header and data below header); 3. Select any cell in new datarange, and then Insert>Table (check 'My table has headers'). Rename table to tLiterals; 4. In formula for Total, replace tData[Values] with tLiterals[Literal]. The formula refers to new table now (This is an example how attach the formula to another existing Table. Of course, when another table was not defined before and there is no need to preserve old one, you can simply replace the header with new one, select any Table cell, activate Design menu, and edit Table name. The formula now refers to column Literals in table tLiterals! And this happens without any formula editing); 5. When you now have another workbook where a table named tLiterals is defined, and which also has column Literals, then you can enter exactly same formula into this workbook too (or copy the formula in Edit mode and paste it into formula window in another workbook. Using Edit mode when copying and pasting is essential for preventing references to another workbook.). As you see, the was no range defined at all! You need to define named ranges when using Tables in case: a) You want to define data validation lists. Data validation can not use Table formula syntax, and can't refer to Tables or Table elements. But you can select e.g. column datarange in Table, and then define the selection as named range. Resulting named range will be dynamic. E.g. when you defined a named range lLiterals, the the formula for it will be '=tLiterals[Literals]'. You can use this named range as source for Data Validation List; b) You need to refer to some part of Table, and you can't do this using Table Formulas. E.g. you have a Table, where some amount of rows at bottom are empty, and you must refer to filled part only. 
Thread Tools  
Display Modes  

Similar Threads  
Thread  Thread Starter  Forum  Replies  Last Post 
Sumif within VBA  LeeAnn  Excel Programming  6  09282017 07:38 AM 
SUMIF with Or  Lluewhyn  Excel  6  02172017 10:48 AM 
Sumif with Conditions  Haha88  Excel  2  12212016 12:08 AM 
Another SUMIF  OTPM  Excel  6  06062016 03:00 AM 
VBA sumif  Nisio07  Excel Programming  0  02262016 07:53 AM 