Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 11-05-2019, 07:57 PM
Karen615 Karen615 is offline Sumif Windows 7 64bit Sumif Office 2010 64bit
Competent Performer
Sumif
 
Join Date: Jun 2011
Location: Chicago
Posts: 112
Karen615 is on a distinguished road
Default 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?
Your help is greatly appreciated.
Reply With Quote
  #2  
Old 11-05-2019, 11:37 PM
ArviLaanemets ArviLaanemets is offline Sumif Windows 8 Sumif Office 2016
Expert
 
Join Date: May 2017
Posts: 869
ArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud of
Default

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?
Reply With Quote
  #3  
Old 11-06-2019, 06:52 PM
Karen615 Karen615 is offline Sumif Windows 7 64bit Sumif Office 2010 64bit
Competent Performer
Sumif
 
Join Date: Jun 2011
Location: Chicago
Posts: 112
Karen615 is on a distinguished road
Default

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
Reply With Quote
  #4  
Old 11-07-2019, 12:22 AM
ArviLaanemets ArviLaanemets is offline Sumif Windows 8 Sumif Office 2016
Expert
 
Join Date: May 2017
Posts: 869
ArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud of
Default

Does this do?
Attached Files
File Type: xlsx SumByCount.xlsx (9.3 KB, 10 views)
Reply With Quote
  #5  
Old 11-07-2019, 07:24 PM
Karen615 Karen615 is offline Sumif Windows 7 64bit Sumif Office 2010 64bit
Competent Performer
Sumif
 
Join Date: Jun 2011
Location: Chicago
Posts: 112
Karen615 is on a distinguished road
Default

PERFECT! Thank you so much!
Reply With Quote
  #6  
Old 11-10-2019, 07:16 AM
Karen615 Karen615 is offline Sumif Windows 7 64bit Sumif Office 2010 64bit
Competent Performer
Sumif
 
Join Date: Jun 2011
Location: Chicago
Posts: 112
Karen615 is on a distinguished road
Default

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.
Reply With Quote
  #7  
Old 11-10-2019, 10:51 AM
ArviLaanemets ArviLaanemets is offline Sumif Windows 8 Sumif Office 2016
Expert
 
Join Date: May 2017
Posts: 869
ArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud of
Default

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.)
Reply With Quote
  #8  
Old 11-11-2019, 07:34 PM
Karen615 Karen615 is offline Sumif Windows 7 64bit Sumif Office 2010 64bit
Competent Performer
Sumif
 
Join Date: Jun 2011
Location: Chicago
Posts: 112
Karen615 is on a distinguished road
Default

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
File Type: png ExcelError.png (23.4 KB, 15 views)
Reply With Quote
  #9  
Old 11-12-2019, 12:25 AM
ArviLaanemets ArviLaanemets is offline Sumif Windows 8 Sumif Office 2016
Expert
 
Join Date: May 2017
Posts: 869
ArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud of
Default

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.
Reply With Quote
Reply

Thread Tools
Display Modes


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

Other Forums: Access Forums

All times are GMT -7. The time now is 02:26 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft