2

I have a SUMIF formula that I can't figure out:

AB1/SUMIF(E:E,E1,AB:AB)

Column E contains text values: Other, Specific

Column AB contains dollar values.

I don't understand how the criteria evaluates to the range. Is it just looking for that value within that range? If so, wouldn't it always evaluate to yes? By always evaluate to yes I mean that for any value that is plugged in, e.g., E3, E5, E10, the criteria is true.

Improve this question
2
  • My understanding is that E:E and AB:AB refer to the entire column as a range.
    – Drew Rush
    May 16, 2014 at 15:55
  • I think the formula is broken. How can you evaluate all of column E against an expression in E1 if that expression evaluates against itself? ">100>100" is a broken logical statement. Are you getting errors on the spreadsheet? EDIT: Nevermind.
    – Wutnaut
    May 16, 2014 at 16:02

3 Answers 3

Reset to default
2

I would translate that into English as: AB1 divided by the sum of things in column AB on rows where the value in column E for that row is equal to the value in E1.

Improve this answer
5
  • So for row two then it would translate as: "...value in Column E for that row is equal to the value in E2"? Because as the rows increase, the row in the criteria also increases.
    – Drew Rush
    May 16, 2014 at 15:58
  • Yes, as the other E:E and AB:AB are columns, only the criteria (E1) and the AB1 would change as the formula is copied down.
    – Jason Aller
    May 16, 2014 at 16:02
  • So then it's just checking if the value in column E for that row, e.g., E1, is equal to the value in E1? So always true?
    – Drew Rush
    May 16, 2014 at 16:05
  • 1
    It is checking to find all rows where the current value in E appears and then returning the sum of the values for the AB column where those match. So if there were six other matches for the value in E# it would return the sum of six values from AB for those rows.
    – Jason Aller
    May 16, 2014 at 16:07
  • Ah. Ok, I see what you're saying now. Thank you!
    – Drew Rush
    May 16, 2014 at 16:08
1

Googled: SUMIF
First result: http://office.microsoft.com/en-us/excel-help/sumif-HP005209292.aspx

Syntax

SUMIF(range,criteria,sum_range)

Range is the range of cells that you want evaluated by criteria.

Criteria is the criteria in the form of a number, expression, or text that defines which cells will be added. For example, criteria can be expressed as 32, "32", ">32", or "apples".

Sum_range are the actual cells to add if their corresponding cells in range match criteria. If sum_range is omitted, the cells in range are both evaluated by criteria and added if they match criteria.

In your case, all values in column E are evaluated against a logical expression in E1, then sums all of AB if that logical expression against E1 is true.

EDIT: I forgot about AB1 dividing the SUMIF, but you guys get the idea.

Improve this answer
4
  • I Googled the answer, but the examples for criteria don't include this particular case. Thanks for letting me know Google exists, however.
    – Drew Rush
    May 16, 2014 at 15:56
  • "Against a logical expression in E1" There's just a text value in E1.
    – Drew Rush
    May 16, 2014 at 16:00
  • Can you be more specific? What is the text? EDIT: nevermind, I get it.
    – Wutnaut
    May 16, 2014 at 16:03
  • Every cell in E that contains a value (it stops at row 300 something) includes either "Other" or "Specific" as a text value.
    – Drew Rush
    May 16, 2014 at 16:04
0

A picture is worth several hundred words.  Imagine that you’re running a hotel, and you ask your guests what state they’re from.

                                                Snapshot of hotel registry implemented in Excel

Column AC is the formula from your question.  It is showing that 20% of the Alaskans in the hotel are in Room 101, 100% of the Californians are in Room 102, 50% of the Texans are in Room 103, etc.

Perhaps a more practical application would have salesmen in Column A and sales in Column AB, so you can see that George Miller has made 42% of the sales in Maine.

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .