14 thoughts on “Multicolumn Sum with Wildcard”

1. Hello,

   FIND is case sensitive, if case sensitivity is not required, use SEARCH instead:

   http://www.xldynamic.com/source/xld.SUMPRODUCT.html#partial

   Regards
   Beate

2. 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?

3. 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

4. Thanks Rob, your formula is great.

   (it didn’t work at first because CO is not C0!)

5. 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?

6. I want to encorp the sumprod function w/ some kind of count function to average over multiple columns & criteria

   Any advice

7. 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

8. 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.

9. Yeah I agree…’search’ would be much better since ‘find’ is indeed case sensitive…

10. 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.

11. 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,”

12. 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

13. 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.

14. @joedbug

    Try

    =SUMPRODUCT(–(1:1>=–”2010-01-01?),–(1:1<=–”2010-01-04?),2:2)

---

Posting code? Use <pre> tags for VBA and <code> tags for inline.