Software

Ask something
Discussion Locked

Excel filter and calculation problem

0 helpful
5 replies

by jimmy

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

Hi,

What i have is this:

Three columns named ITEM----COST----WEIGHTING

I have set some criteria for each of these columns e.g. ITEMS are hats, scarves, boots etc.

For the COST i have made up some simple values e.g. some scarves cost ?3.00 others cost say ?1.70

and for the WEIGHTING column i made either 1, 2 or 3 depending on how important i think they may be.

I have filtered the data so it has a drop down list ok with me so far.
I have inserted some random items with randomcosts and weighting and all items is ?180.40.

What i want to be able to do is click on the drop down filter and select only items with a 1 weighting for example and have the cost automaticaaly update to the cost of the current filtered items.

I hope you understand what i mean by this and once again any help/advice very much appreciated.

P.S.
I cannot do it in a database as the person it is for is not used to them. Also i know there is a command in Excel named SUMIF but can't seem toget it to do what i want it to.

Cheers.

5 replies

by dklippert

Here's the answer:
Outside of the filtered list, (Above the labels)
enter this formula:

=SUBTOTAL(9,C:C)

This assumes that the "Weighting" is in column C

=SUBTOTAL(Function_num,Range)
Function_num is the number 1 to 11 that specifies which function to use in calculating subtotals within a list.

1 AVERAGE
2 COUNT
3 COUNTA
4 MAX
5 MIN
6 PRODUCT
7 STDEV
8 STDEVP
9 SUM
10 VAR
11 VARP

BTW I received a note saying that your e-mail address is incorrect.

by jimmy

Thanks for the help DKlippert. I have managed to get the answer i was looking for by simply doing a variant of what you had.

by dklippert

You'll have less problems if you put the totals above or to the side of the list, but here is one way to do it:

Move the Label Total to Cell C21
Type the Formula =SUM(B4:B18) in D21
In C22 type Total of Filter.
In D22 type =SUBTOTAL(9,B:B)

*****
(=SUBTOTAL(Function_num,Range)) is how the formula is constructed. You can look it up in the Help file to find out what the Function_num should be. 9 is for Sum.

(Have you fixed the problem with your e-mail address? It appears that it belongs to someone else)

by jimmy

Hi thanks for the assistance Dklippert. Unfortunately, i can't get this to work.
I have the following:
A3 cell has the title Item. //filtered
B3 cell has the title Cost. //filtered
C3 cell has the title Weighting. //filtered
A4-A18 cellshave values e..g hat, scarve, gloves etc
B4-B18 cells have the cost of each item
C4-C18 cells have the wighting for each item
A21 cell has the been inserted with the name totalcost
B21 has =SUM(B4:B18) //the total cost of all the items

Question - Where do i place the =SUBTOTAL(9,C:C) you said to place in? I am putting it in cell C2.
Question - Do i need to change the formula =SUBTOTAL(9,C:C)
Question - You have also said =SUBTOTAL(Function_num,Range)which cell would i place this in?

Many thanks

This discussion is closed to new replies.