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.

Thanks in advance for any help you can provide.

Jeff B

Should be

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

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

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

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

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

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

@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

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.

@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