Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 141
  • Last Modified:

Excel: Sum columns using multiple criteria (sumifs variant)

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.

Thanks for your help
ee-Factory.xls
0
dabug80
Asked:
dabug80
  • 4
  • 2
1 Solution
 
Wilder1626Commented:
Hi

Is that what you are looking for?
ee-Factory-no1.xls
0
 
Wilder1626Commented:
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))

Open in new window


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
 
dabug80Author Commented:
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
Wilder1626Commented:
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
 
dabug80Author Commented:
Great. Works exactly how I wanted. Cheers.
0
 
Wilder1626Commented:
I'm glad i was able to help :-)
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now