x
Solved

# Excel: Sum columns using multiple criteria (sumifs variant)

Posted on 2015-02-23
Medium Priority
147 Views
Hi,

I would like to calculate the attached yellow table. I think it's a sumifs formula - but it needs to only do this for the given date range.

ee-Factory.xls
0
Question by:dabug80
• 4
• 2

LVL 11

Expert Comment

ID: 40627161
Hi

Is that what you are looking for?
ee-Factory-no1.xls
0

LVL 11

Expert Comment

ID: 40627169
Another way could be like this:
Example:

If you put this formula in Cell J7 :
``````=IF(SUMIFS(B:B,A:A,J3)=0,"",SUMIFS(B:B,A:A,J3))
``````

With the above formula, if no records was found, it would leave the cell empty instead of putting N/A# or 0
ee-Factory-no2.xls
0

LVL 1

Author Comment

ID: 40627179
Thanks Jean-Marc,

This works, but I'm hoping for a formula that doesn't define/lock the date column - but rather searches for the appropriate column based on the given date in the yellow cells. By doing it this way, I don't have to worry about users changing the dates on either of the tables.

Is this possible?
0

LVL 11

Accepted Solution

Wilder1626 earned 2000 total points
ID: 40627224
Is this more what you are looking for?

It will look at all dates columns

Just remember to press Ctrl+Shift+Enter after typing the formula, instead of just Enter. This is to activate the array formula rather than a single data value.
ee-Factory-no3.xls
0

LVL 1

Author Closing Comment

ID: 40627325
Great. Works exactly how I wanted. Cheers.
0

LVL 11

Expert Comment

ID: 40627871
I'm glad i was able to help :-)
0

## Featured Post

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.