Search code examples
excelnumber-formattingsumifs

Excel SUMIFS over a range of numbers, which are formatted as strings

I am using an automated query in Excel, which queries TFS data from a server. This data exists in a sheet called "TFSData". On another sheet I am running some statistic evaluations, for example counting verified stories per sprint or other gimmicks. I am Using COUNTIFS or SUMIFS formulas to get the results.

Now I am trying to use a SUMIF to get an "added value" per sprint from numbers given in the TFS Data. Here I come into trouble, because the column with the "Added value" numbers is formatted as text. I have no chance to manipulate this data, since the changes get lost whenever the query is rerun.

An additional helper column does not work either, because newly inserted rows after a refresh would not contain any formulas - this needs a lot of manual rework after a refresh.

The actual formula for a cell looks like this:

=SUMIFS(TFSData!J3:J352; TFSData!N3:N352;"*Sprint 212": TFSData!D3:D352;"Verified");

It produces "0" results. The "J" column is the one containing the string values, like "3", "5", etc.

I would like to use something like: 

=SUMIFS(VALUE(TFSData!J3:J352);TFSData!N3:N352;"*Sprint 212": TFSData!D3:D352;"Verified");

Any ideas?

The solution looks like this:

=SUMPRODUCT(TFSData!J3:J352+0;--ISNUMBER(SEARCH("Sprint 212";TFSData!N3:N352));--(TFSData!D3:D352="Verified"));
Solution

  • You have a few options:

    1) Use SUMPRODUCT, e.g.

    = SUMPRODUCT(TFSData!J3:J352+0; (RIGHT(TFSData!N3:N352,10)="Sprint 212")+0; (TFSData!D3:D352="Verified")+0)

    (Note the first +0 in the formula above is to convert text to numbers, the other +0's is to convert an array of TRUE/FALSE values to an array of 1/0 values which is necessary for SUMPRODUCT.)

    2) Change your query to return a numeric type rather than "text that looks like numbers", then you could actually use your SUMIFS formula.

    3) Better yet, change your query to return this sum result. That way your query returns exactly what you want instead of needing to perform an extra operation in Excel.

    But I don't know the whole story, maybe options 2) and 3) aren't feasible because you're calling a stored procedure and in that case you can't alter the query. Just noting that in general I almost always find it easier to let the query perform all the work and do as little work as possible within Excel.