Microsoft Office Forums Sumif
 Register FAQ Search Today's Posts Mark Forums Read

#1
11-05-2019, 07:57 PM
 Karen615 Windows 7 64bit Office 2010 64bit Advanced Beginner Join Date: Jun 2011 Location: Chicago Posts: 49
Sumif

In cell range A3:A100, the user can choose from a drop-down 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?
#2
11-05-2019, 11:37 PM
 ArviLaanemets Windows 8 Office 2016 Expert Join Date: May 2017 Posts: 508

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
11-06-2019, 06:52 PM
 Karen615 Windows 7 64bit Office 2010 64bit Advanced Beginner Join Date: Jun 2011 Location: Chicago Posts: 49

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
11-07-2019, 12:22 AM
 ArviLaanemets Windows 8 Office 2016 Expert Join Date: May 2017 Posts: 508

Does this do?
Attached Files
 SumByCount.xlsx (9.3 KB, 6 views)
#5
11-07-2019, 07:24 PM
 Karen615 Windows 7 64bit Office 2010 64bit Advanced Beginner Join Date: Jun 2011 Location: Chicago Posts: 49

PERFECT! Thank you so much!
#6
11-10-2019, 07:16 AM
 Karen615 Windows 7 64bit Office 2010 64bit Advanced Beginner Join Date: Jun 2011 Location: Chicago Posts: 49

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?

#7
11-10-2019, 10:51 AM
 ArviLaanemets Windows 8 Office 2016 Expert Join Date: May 2017 Posts: 508

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
11-11-2019, 07:34 PM
 Karen615 Windows 7 64bit Office 2010 64bit Advanced Beginner Join Date: Jun 2011 Location: Chicago Posts: 49

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.
Attached Images
 ExcelError.png (23.4 KB, 9 views)
#9
11-12-2019, 12:25 AM
 ArviLaanemets Windows 8 Office 2016 Expert Join Date: May 2017 Posts: 508

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 Linear Mode

 Similar Threads Thread Thread Starter Forum Replies Last Post Lee-Ann Excel Programming 6 09-28-2017 07:38 AM Lluewhyn Excel 6 02-17-2017 10:48 AM Haha88 Excel 2 12-21-2016 12:08 AM OTPM Excel 6 06-06-2016 03:00 AM Nisio07 Excel Programming 0 02-26-2016 07:53 AM

All times are GMT -7. The time now is 09:13 PM.

 -- Default Style -- Lightweight -- New Mobile Contact Us - Privacy Statement - Top