Hi.
I have been trying to make excel sum values from different columns based on a choice made through a validation drop down menu. Basically let's say I choose E in the B1 drop down, I'd want the formula in B2 and C2 to sum each of their own values, so B2 would sum D2 and F2 while C2 would sum E2 and G2.
I've provided an excel that hopefully clears up what I'm trying to do, been attempting to use various =SUMIFS but had no success.
Hope this makes sense and thank you
is something like this what you are looking for? =IF(B1="D",SUM(D2:E2),IF(B1="E",SUM(F2:G2),if... etc.
Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
Sam Capricci
Kind off, but that formula would sum D2 and E2 for D and F2 and G2 for E. What I'm looking for is something like, let's say I choose D in the drop down column, then in the b2 field it would only show d2 value, and in the c2 field the e2 value would be shown.
Then if I choose E in the drop down menu, in B2 it would sum the D2 value with the F2 value, skipping the E2 value in between. And C2 doing the same with E2 and G2, skipping the F2 in between.
Please ignore this double post
Last edited by heneli627; 10-07-2015 at 05:16 AM.
In posts 1 and 3 you mention summing values depending on the dropdown selection.
Then you mention that (also in post 3) that you "seem" to want a simple reference of if/then. Like this... =IF(B1="D",D2,IF(B1="E",F2)) etc.
So I'm not clear. Perhaps you could give a few examples?
Mm, I think you got it for the first part, If you choose D in the drop down menu the cells would only be referenced, but then if you'd choose E, the previous cell D would be summed with the new one, so D2 and F2 would be summed, then if you chose F, D2 and f2 and h2 would be summed.
So it would start with a reference but with the next option become a sum thing, does that make sense?
Still not clear. I will be offline for a while so I'll check back later to see if someone has helped you.
Provided an image, hope it helps
Skärmavbild 2015-10-07 kl. 11.51.17.png
It looks to me like you have two entries for each identifier. In column B, you want to sum up the 1st entries up to the indicated identifier, and in column C you want to sum up the 2nd entries, does that sound correct?
I think I would add a helper row so that I can help Excel see the 1st and 2nd entries. Then my conditional sum can be a simple sumif() or sumifs(). Maybe add a row of 1,2,1,2,... in the 4th row. Then my sumifs() can be something like =sumifs(row5,row3,"<"&choice,row4,"=1").
Originally Posted by shg
So if we were to use the picture above as an example, I'd put 1,2,1,2 under the letters to help excel differentiate? But I'm not sure how to use =sumifs to help recognize what letter I choose in the drop down menu.
When I first encounter an unfamiliar Excel function, I start by reviewing the help file for that function: https://support.office.com/en-us/art...6-611cebce642b (there should be a similar help file in your Excel installation). This page will explain the syntax for using the function, descriptions of the required arguments for each function, and examples of how to use the function.
If I need additional examples, usually a quick internet search for "excel sumifs function" will reveal multiple pages with different examples of how to use the desired function.
So, in the example we are creating, your sum_range would be row 5. Rows 3 and 4 would be the two criteria ranges. The criteria associated with row 4 will be either 1 or 2 depending on which one you want summed. The criteria associated with row 3 would be a text string "<"&B3 (& is the concatenate operator that takes two text strings and concatenates them together).
Does that help you understand how to use SUMIFS()?
Thank you for the explanation, I'd like to say I understand it but... I'm not getting it to work. There's something wrong the criteria range I've put, but I can't figure out what.
The sumifs I used is
Please Login or Register to view this content.
I think it is the difference between "<" and "<=". The former does not find the cases that =D, only those that precede D (A,B,C). See if using "<=" (which should include the "D" columns) corrects the problem.
That seems do have done the trick
Back to having issues, I've changed the D letters to month names, which I believe the code does not recognize properly. Have provided the workbook I'm using.
Try this, I reformatted your date cells to make the "<=" work. Less than or equal to won't work with text. So I changed each of your months to dates - generalized to 1/1/2015 for January and 2/1/2015 for February etc.
That did it, thank you!
Great, don't forget to mark the post as solved using the thread tools dropdown at the top of the post. And thank you for the reputation.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks