# SUMIFS producing #VALUE!

1. ## SUMIFS producing #VALUE!

I am trying to calculate the number of deaths from tornadoes on various days. The problem is that the death toll is not always known, so I am using ? for that day. For days which are all ? marks, I would prefer to get just a blank cell or simply (missing) if at all possible. If there are mixed values of ? marks and values, I would prefer to just count all of the deaths on that day and come back with a total death toll. I am using the following equation.

=SUMIFS(\$D\$2:\$D\$38,\$I2,\$E\$2:\$E\$38)

To further complicate things, some of my dates are in the 1800s. It should not be an issue because I am using the same date though.

I have attached a sample of my spreadsheet.

Jeff B

2. ## Re: SUMIFS producing #VALUE!

Should be

=SUMIF(\$D\$2:\$D\$38,\$I2,\$E\$2:\$E\$38)

or
=SUMIFS(\$E\$2:\$E\$38,\$D\$2:\$D\$38,\$I2)

3. ## Re: SUMIFS producing #VALUE!

Thanks for the help!

Jeff

4. ## Re: Solved: SUMIFS producing #VALUE!

or even =SUMIF(\$D:\$D,\$I2,\$E:\$E) . For SUMIFS whole column could be used also but as a rule the performance is worse in this case because sumifs doesn't use used range. May be MS fix it in the future.

5. ## Re: Solved: SUMIFS producing #VALUE!

Originally Posted by BMV
because sumifs doesn't use used range
Is there a link to sum official documentation you can provide to support this? Or is it a result of some tests you yourself have carried out?

Regards

6. ## Re: SUMIFS producing #VALUE!

I have done it for you this time - there is no need to edit the thread title.

7. ## Re: Solved: SUMIFS producing #VALUE!

@XOR LX . Yes it's result of tests but now i can't reproduce it . It's looks likу it was fixed. I get differences only for countifs function and only for several condition
For 5000 calculation
=COUNTIFS(A:A;1;B:B;2) 18,80469 s
=COUNTIFS(\$A\$1:\$A\$200001;1;\$B\$1:\$B\$200001;2) 14,54297 s

8. ## Re: SUMIFS producing #VALUE!

Hi BMV, the issue you outlined applies only to the COUNT function, not the SUM.

the attached is taken from an old thread but something I use to highlight the issue when it comes up for discussion - as this isn't documented.

The 5k COUNTIFS will calc in ~17s (on my machine) whereas SUMIFS will take 0.1s
(sometimes, if being used in large volume, I will advise people to add the 1 field so they can SUMIFS in preference to COUNTIFS)

FWIW, @XOR LX corrected me to the same effect as I saw the issue with COUNTIFS and presumed it applied to all - but it does not (thankfully!)

9. ## Re: SUMIFS producing #VALUE!

@XLent Hi. it could be my mistake (sum -count)

10. ## Re: SUMIFS producing #VALUE!

Thanks, XLent, BMV.

Now we just need to understand why there is such a glaring discrepancy with respect to calculation time for these two functions of the same 'family'! Why would MS design it such that one operates over the used range only but not the other?

Cheers

11. ## Re: SUMIFS producing #VALUE!

@XLent

Apologies for my VBA ignorance, but could you shed some light on why the calculation times given by your procedure differ from those obtained by performing a full manual workbook recalculation?

Perhaps it's related to this, as given at decisionmodels.com:

"[In Manual Calculation Mode, VBA Range.Calculate] calculates all the cells in the selected range left-to-right and top-to-bottom. Excel 97 and Excel 2000 ignore dependencies within the selected range, but Excel 2002 and Excel 2003 follow the left-to-right and top-to-bottom calculation with a dependency recalculation of the selected range only.
This means that range.calculate can be significantly slower in Excel 2002 and 2003 than in Excel 97 and 2000, and will give different results if there are dependencies within the range being calculated that are not left-to-right and top-to-bottom.
"

Presume that newer versions of Excel operate similarly to Excel 2003 in this respect, as a full manual recalculation of the COUNTIFS formula range takes significantly less than that obtained using the BasicTimer sub.

Of course, this does not in any way detract from the main point you're making, i.e. that SUMIFS is far more efficient than COUNTIFS.

Regards

12. ## Re: SUMIFS producing #VALUE!

Interesting.... and, I honestly don't know but, reading one of Charles Williams' white papers (he of Decision Models):

Range.CalculateRowMajorOrder calculates the range left to right and top to bottom, ignoring all dependencies.
Range.Calculate calculates the range resolving all dependencies within the range.

implying that, were this the cause, changing to the former method should result in a big performance gain -- but I didn't experience that (very similar)

I might create a new thread on this, fear we hijack the thread and incur the wrath of a Mod (or two...)

13. ## Re: SUMIFS producing #VALUE!

Offtop
XOR LX, XLent could you test vlookup function also. I have info about catastrophic dependency from range in the new version of Excel. Unfortunately there is article in Russian but
Способ 1. ВПР is VLOOKUP(B2,\$G\$2:\$H\$600,2,0) 4,3 s
Способ 2. ВПР с выделением столбцов целиком VLOOKUP(B2,\$G:\$H,2,0) -14,5 s

Dear moderator if it is not possible to discuss here sorry. I agree with XLent it could be new thread.

14. ## Re: SUMIFS producing #VALUE!

@BMV Interesting - I'll get back to you on that one.

Re SUMIFS vs COUNTIFS, I have discovered the following:

First, note that COUNTIFS performs as well as SUMIFS when there is only one criteria_range (and by definition COUNTIF performs equally well). The large differences in performance occur when there is more than one criteria_range.

I would hypothesize that the sum_range is always used to determine the used range for SUMIFS.

For COUNTIFS, I assume that, if there is only one criteria_range, then that is used to determine the used range. When there is more than one criteria_range, it is not clear what algorithm is being employed to determine the used range. However, we can be sure that one is being employed: you only have to compare COUNTIFS to SUMPRODUCT using entire column references to see that, as bad as it is compared to SUMIFS, COUNTIFS is nevertheless clearly not processing all 1,048,576 rows as SUMPRODUCT has to.

Also, there does not appear to be any difference in performance for COUNTIFS with 2, 3, 4, ..., i.e. an arbitrarily large number of criteria_ranges: they all perform equally badly compared to the version with a single criteria_range.

What we need to understand is how COUNTIFS is determining the used range in the multi-criteria_range case.

Regards

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

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1