0

I would like to calculate a column based on selections from two other columns with drop down menus.

For example :

In one column, "C" you have 3 options (A team,B team,C team), In column "F" you have the day of week to schedule (Monday, Tuesday, Wednesday, Thursday, Friday) and in Column "E" I would like to put how many minutes total each line is.

This will calculate how many minutes worth A team has on Monday.

I have been successful using SUMIFS code to narrow by the first set of criteria

i.e. =SUMIFS(E2:E20,C2:C20,"A team")

When I try to add a second set of criteria, it doesn't work.

How can I achieve my goal here? Should I be trying something different?

I have tried just adding the criteria like so:

=SUMIFS(E2:E20,C2:C20,"A team",F2:F20,"Monday") ....... but it wont work!

Thanks,

Tyler

3
  • @Scott, the question to which you are referring was also asked by Tyler, so I assume the answer did not work and I think that I see why. @Tyler, from this question it seems like you want the formula to put values in cells E2:E20, is that correct? If so you'll need to change the first argument ("Sum Range") to the column that the values come from. The first example of Excel Help on the SUMIFS function should help you to understand what "Sum Range" should point to. If you need more assistance it would be helpful if you could include a screen shot of at least part of your data.
    – Clif
    Jun 7 '15 at 22:35
  • Please do not repost your question if you do not get a useful answer right away. If an answer you receive does not work for you, you may indicate that in a comment on the answer. That way, the person who answered may be able to help you, or someone may see that you are still looking for help and post a separate, better answer. FYI, this question will be closed as a duplicate to your first question and possibly be deleted. In the meantime, feel free to take a tour of the site to learn how Super User works.
    – Excellll
    Jun 17 '15 at 20:58
  • 1
    @Clif, removed my comment
    – Dave
    Jun 22 '15 at 20:29
1

one way you might do it is as follows:

Insert a new column wherever appropriate (it will be hidden afterwards). Suppose you put it in column G. In these cells concatenate Column C and F, for example in G2, =CONCATENATE(C2,F2).

Then your sumifs formula should be =SUMIFS(E2:E20,G2:G20,"A teamMonday").

You can now hide column G.


As a side note, you can of course use concatenate inside your SUMIFS, eg

A1: B team

A2: Friday

A3: =SUMIFS(E2:E20,C2:C20,CONCATENATE(A1,A2)).

Good luck.

Not the answer you're looking for? Browse other questions tagged or ask your own question.