Multicolumn Sum with Wildcard

It is possible to sum a column of values on the condition of other column’s values by using the SUMPRODUCT function.
This is much the way SUMIF works, but on more than one criteria column.

SUMIF has the ability to perform criteria matching with Wildcards, something that SUMPRODUCT isn’t built to do.
The following describes how to do Wildcard SUMPRODUCT.

In this example, I use three columns: Place, Season and Value.
I have four named ranges:
P_Rows
=COUNTA(Sheet1!$A:$A) – COUNTA(Sheet1!$A$1:$A$6)
P_Place
=OFFSET(Sheet1!$A$6, 1, 0, P_Rows)
P_Season
=OFFSET(Sheet1!$B$6, 1, 0, P_Rows)
P_Value
=OFFSET(Sheet1!$C$6, 1, 0, P_Rows)

Just taking a sidestep for a moment to discuss the non-wildcard method:
Imagine that the Place in cell A3 is Auckland.
The formula in cell C3 could be:
=SUMPRODUCT(($A$3 = P_Place) * ($B$3 = P_Season), P_Value)

But that would not give you wildcard ability.

The formula, which was used in the screenshot, is as follows:
=SUMPRODUCT(ISNUMBER(SEARCH(C0 & $A$3 & C0, C0 & P_Place & C0)) * ($B$3 = P_Season), P_Value)

SEARCH returns a number when there is a match, #VALUE! when there is no match. ISNUMBER converts this to TRUE/FALSE.
The act of multiplying Booleans together results in 1 or 0, which in turn SUMPRODUCT multiplys with P_Value and Sums.

You might now ask “what is that C0 & $A$3 & C0 about?”
It’s used to match the whole value, not just a partial match.
Consider the effect if it were just ISNUMBER(SEARCH($A$3, P_Place)). A Place called Cromwell would also be a positive match for Well*

C0 is a named range with the formula:
=CHAR(31)
Ascii character 31 is not likely to appear in your list of Places.
You could just embed CHAR(31) in the formula – I’ve chosen to use a named range just to reduce the formula length.

Posted in Uncategorized

14 thoughts on “Multicolumn Sum with Wildcard

  1. Maybe my comments are totally inadequate, but I have always seen the Search (and Find) fuctions apply to strings in one cell at a time. Here one searches a whole range of cells. Is this a novelty?

  2. Beate: I should have guessed xldynamic.com had covered it ;) — keep in mind the goal was to make a super SUMIF, which too is case insensitive.

    Frank: Most functions tend to work on one cell at a time. Many which ask for a single cell can actually process an array instead, returning an array.
    In the example above, highlight D7:D18 and enter the following as an array formula (CTRL+SHIFT+ENTER):
    =SEARCH(C0 & $A$3 & C0, C0 & P_Place & C0)
    The formula in C3 could then be:
    =SUMPRODUCT(ISNUMBER(D7:D18) * ($B$3 = P_Season), P_Value)

    Dont know if that helps any.

    Cheers,
    Rob

  3. I know I might seem a bit dim, but I couldnt get the above to work. The named ranges seemed to be messing the whole thing. But if you wanted to do the same thing without name ranges the following in a cell will search for all “well”s in summer:

    =SUMPRODUCT(–(ISNUMBER(SEARCH(“well”,A7:A18))),–(B7:B18=”summer”),–(C7:C18))

    Perhaps a downloadable xls file for us stupid ones?

  4. Please Can someone help me with the following ( i have spent ages trying to work it out):-

    TypeQuantity

    Car-White6
    Car-Red2
    Car-Silver4
    Van_white4
    Car-White1
    Van-Yellow3
    Van-Blue7
    Van-White5
    Van-Red2
    Car-White6

    I need the formula to find:
    No of White cars
    No of Vans

  5. Debbie,

    Let’s say you have, in column A, all your type. In column B, you have the quantity. This takes up 10 rows of data.

    Below that, I put the word “White” in A13. In A14, I type (=SUMIF(A1:A10,”*”&A13&”*”,B1:B10). The asterisks before and after A13 (white) opens that criteria to SUMIF for anything that contains “White.” Similarly, I substitute “Van” for “White” in A13 (or just replicate in the row below) and I can sumif that way.

  6. I am looking to sum entries in a column that are not a date.

    So for this row I would like to get the sum 357

    | Column:A | Column:B | Column:C | Column:D |
    ROW:2 | 1/1/2008 | 234 | 1/2/2008 | 123 |

    I want to do something like
    sumif(A2:2,isnumber(currentcellsomehow),A2:2)

    Does any one know how this is possible, I have been reading posts on
    the web but can’t find anything like this.

  7. Steve,
    Because Excel stores dates as date/time serial numbers, you might be able to distinguish between dates and the numbers to be summed based on size. For example, all numbers larger than 30,000 represent a date on or after February 18, 1982. If your numbers are smaller than 30,000 and the dates occur after that point, you could use a less than criteria to sum the values while excluding dates:
    =SUMIF(2:2,”

  8. i want to use countif to count a range of cells if it contain 1,using date range as a criteria that is
    (1)check if the date is between a certain range of date if it is then (2)count the row if it contain 1.
    this how the layout is like
    | A | B | C | D | E | F |
    1| dates | |01/01/2010 | 02/01/2010 | 03/01/2010 | 04/01/2010 |
    2| numbers| | 1 | 0 | 1 | 1 |

    check if the date falls between 01/01/2010 and 04/01/2010 . then count row 2 if it contain the value of 1
    please i need the formula to do that
    thanks

  9. HI! I have a small knowledge about Excel. I need some help from all of you using COUNTIF on SPECIFIC DATE RANGE. I have WORK PERMIT ENTRY in Sheet1 and Weekly Report on Sheet2. All I need is to count the number of permits per remarks in weekly basis.

    In WORK PERMIT ENTRY (Sheet1), Column B, where I input the date of the permit that I’d received. In Column U, where I input Remarks such as ON WORK, EXTENDED, EXTENDED/ISSUED, CLOSED, CANCELLED, NEW, HOLD, and EXPIRED.

    In Weekly Report I have those remarks as shown below; I need a formula that will automatically count the Remarks from Sheet1 Column U in weekly basis (source of DATE from Column B) by inputting the beginning date(FROM) “dd/mm/yyyy” in a cell below “FROM:” and inputting end date “dd/mm/yyyy” in cell below “TO:”. IS IT POSSIBLE?

    Example:

    FROM:TO:
    13/02/1014/02/1015/02/1016/02/1017/02/1018/02/1019/02/10

    ISSUED /
    ON WORKEXTENDED EXTENDED/ISSUEDCLOSEDCANCELLEDNEWHOLDEXPIREDTOTAL
    1511 10120030
    50.00%3.33%3.33% 33.33%3.33%6.67%0.00%0.00%100.00%

    The output of the formula will automatically update my PIE GRAPH.

Leave a Reply

Your email address will not be published. Required fields are marked *