# SUMIFS problem

1. ## SUMIFS problem

Hi folks,

I spent a long while looking but still not exactly sure what I did wrong.
My understanding is that SUMIFS sums up the value in selected cells that fulfills multiple conditions.

Here's my problem, I have a list of values listed under each week (for more legible representation of the table, please refer to imgur.com/a/GBFo4Zj)
1-Jul-20 8-Jul-20 15-Jul-20 22-Jul-20 29-Jul-20 5-Aug-20 12-Aug-20 19-Aug-20 26-Aug-20 2-Sep-20
-\$275.00 -\$275.00 -\$275.00 -\$275.00 -\$275.00 -\$275.00 -\$275.00 -\$275.00 -\$275.00 -\$275.00
-\$30.13 \$0.00 \$0.00 \$0.00 -\$30.13 \$0.00 \$0.00 \$0.00 -\$30.13 \$0.00
\$0.00 \$0.00 \$0.00 -\$19.99 \$0.00 \$0.00 \$0.00 -\$19.99 \$0.00 \$0.00
\$0.00 \$0.00 \$0.00 \$0.00 \$0.00 \$0.00 \$0.00 \$0.00 \$0.00 \$0.00
\$0.00 -\$110.26 \$0.00 \$0.00 \$0.00 -\$110.26 \$0.00 \$0.00 \$0.00 \$0.00

Assuming that the first column for 1-Jul-20 is in cell C4, the cells I wanted to calculate is cell C5 to C19, and that, in a separate sheet, I have the date criteria in cell B5.
The formula I entered was =SUMIFS('Sheet7'!C5:C19, 'Sheet7'!\$4:\$4, B\$5)
It returns #VALUE, which really doesn't really help much.

Any guidance will be appreciated. Thanks.

2. ## Re: SUMIFS problem

It's always best to actually attach a sample file (please read the yellow banner at the top of this page), but failing that, maybe try this formula:

=SUMPRODUCT(('Sheet7'!C5:M19)*('Sheet7'!C4:M4=B\$5))

3. ## Re: SUMIFS problem

Your title is not great, but I will let it go this time.

The ranges in the formula need to be exactly the sane length for it to work and need to be on the same axis - you are trying to compare a row with a column, so it will never work.

Welcome to the forum.

Although we value your privacy as much you do, it could be important that members have a rough idea of your location as the solutions they offer may be affected by your locale. For instance, you might in the future post questions which are related to your regional settings.

With this in mind, please update your profile to something more precise (something such as UK, Europe, USA, UAE, etc. will suffice).

4. ## Re: SUMIFS problem

Originally Posted by Gregb11
It's always best to actually attach a sample file (please read the yellow banner at the top of this page), but failing that, maybe try this formula:

=SUMPRODUCT(('Sheet7'!C5:M19)*('Sheet7'!C4:M4=B\$5))
Thanks, Greg, here's a sample sheet.
In the meantime, I will give that a go.

I am aware that if the cell with the value I want to sum up is fixed, I can always use SUM(), but I am curious why SUMIFS did not work in this instance.

AliGW,

Not entirely sure how to phrase the title as I couldn't understand what went wrong.
Appreciate your explanation, but I am not quite sure what you mean since what I am intending to do is sum the column if a cell matches another cell.

5. ## Re: SUMIFS problem

=SUMIFS('Sheet7'!C5:C19, 'Sheet7'!\$4:\$4, B\$5)

In the formula you have a vertical sum range of 14 cells in length. The criteria range is the whole of row 4, which is horizontal. SUMIFS requires ranges that are parallel to each other and of the same length, so your formula is obviously wrong. Does this make sense?

Not entirely sure how to phrase the title as I couldn't understand what went wrong.
Your title is meant to tell us what you are trying to do, not how it should be done or why what you have tried is failing - your helpers will deal with that.

6. ## Re: SUMIFS problem

In B5 copied across:

=SUMPRODUCT((Sheet7!\$C\$3:\$AP\$3='Summary Tracking'!\$B\$1)*Sheet7!\$C\$21:\$AP\$21)

In B6 copied across:

=SUMPRODUCT((Sheet7!\$C\$3:\$AP\$3='Summary Tracking'!\$B\$1)*Sheet7!\$C\$5:\$AP\$19)

There are currently 1 users browsing this thread. (0 members and 1 guests)