some basic questions on sumif, if with arrays
Posted by Figen on December 28, 2000 2:34 PM
Hi,
these may be really boring for some of you but I am just learning excel and would appreciate your help.
name age salary car
allan 13 1000 audi
tracy 12 1200 opel
jenny 11 1000 opel
tammy 23 3400 audi
nick 45 4500 saturn
bruce 34 5600 bmw
hillary 24 1300 mercedes
eileen 25 1000 saturn
andrew 36 1200 porsche
I have the above lists that I created for practice and I would like to
1) sum up the salaries of people who are younger than 24. But I want to use 24 as a variable; meaning I also
want to be able to use the same function for different
ages by referencing another cell that has another age
stored in it.
2)list all the names of the people who earn less than or equal to $1200. I tried to do this as an array
formula but it did not work right.
3)Find the names of people who have any one of the cars listed in a different column. For example if I have a list in a column
saturn
porsche
fiat
audi
I want to find the list of the people who own these
cars but this cars list may get really long so I want
to find a way of doing this without writing individual
if statements that match to the car names.
Many thanks in advance....
Posted by Robert on December 28, 2000 3:03 PM
for the sum of the salaries of people <24 use a cse formula: =SUM(IF(B5:B7<25,C5:C7)) where b5:b7 is the age and c5:c7 is the range to be summed. Enter the formula with ctl shift enter, the way to put in an array formula, see one of the tips on this board...
the second question is solved by use of autofilter the list and selct custom filter <1200 for the salary column.
The third question is easily solved by using a pivot table organized by car type. Double click on the car of choice, and voila, a new sheet with only those car owners is generated. Autofilter works here as well as a second solution...
When working with Excel, make sure it is a list you are working on, see help what is a list...
R.
Posted by Figen on December 28, 2000 3:22 PM
Thanks Robert for the answers, it is quite helpful. However in the first question, what I wanted to do was to make the 25 a variable. I used the sumif formula but instead of writing <25, I want to refer to a cell that has 25 written in it so I can write a row of different ages and then copy this formula so each time it refers to an adjacent cell that has a different age stored in it. Do you have any suggestions for this? Thanks again....
Posted by Aladin Akyurek on December 28, 2000 5:52 PM
I assume that the label "name" is in A1, "age" in B1, etc.
Ad 1.
Enter in F2 the age criterion 24. Name F2 "Age24" via the Name Box.
Enter in
G2 =SUMIF(B2:B10,"<"&Age24,C2:C10)
Ad 2.
Select H2:H10, then control+shift+enter the formula
=IF(C2:C10<=1200,A2:A10,"")
Ad 3.
Type in
E2 = A2 which is necessary for VLOOKUP to work, because it works from left to right. Copy the formula in E2 to E3:E10.
Type now your set of saturn, porsche, fiat, and audit in the range J2:J5.
Select the range K2:K5, then control+shift+enter the array formula
IF(ISNA(VLOOKUP(J2:J5,$D$2:$E$10,2,0)),"",VLOOKUP(J2:J5,$D$2:$E$10,2,0))
Aladin
Posted by figen on December 28, 2000 6:43 PM
Thanks Aladin,
Your solutions were exactly what I was looking for. The only additional question is that in solution 3, it only brings up the first owner for a certain type of car whereas there are multiple people who own a certain type of car. Any quick solutions for that?
Posted by Dave on December 28, 2000 8:47 PM
Hi Figen
If I understand you correctly there is a few ways to do this.
1. Click In cell F1 and go to Data>Validation then select "List" from the "allow" box. Now highlight you column of Ages, include some blank rows as they wont appear in your list until they contain data. Set the "Input Message" and "Error Alert" if needed. Click Ok.
Now put an array like this in any cell:
=IF(F1="","",SUM(IF(A1:B20>F3,D1:D120)))
-OR If you only need to check a single Column then you could use the standard SUMIF like:
=SUMIF(A1:A20,">" &F1,D1:D20)
2.Click within your table and go to Data>Filter>Autofilter. Now in a cell that won't be hidden when you filter your rows put:
=SUBTOTAL(9,D2:D20)
Select the filter on your age column and select "Custom" and set the criteria.
SUBTOTAL will only sum visible cells. The number 9 represents the Sum function.
Dave
- OzGrid Business Applications
Posted by Robert on December 29, 2000 11:32 AM
If you highlight (i.e. select) the 25 in the formula and then click the cell where the age is stored, the 25 is replaced by the cell reference.
OK?
Posted by Aladin Akyurek on December 29, 2000 4:46 PM
I knew you would come back on Ad 3 (See also
7521.html,
where the same issue turns up).
You get (thru e-mail) a solution that I developed in response to a similar question posted at this board.
Aladin
