Sum several colums using wildcard

[TheJackal]

Hi,


Awesome forum!


I am currently working on a sheet for my small business. It is a chart used to look at current and coming expenses. The final version will look something like this (design will be done later):

In row 3 I want the sum of all "costs" within that month to be summed automatically.
Right now I am using =SUMIF with "cost*", and that works, but it only looks in the first column which means I have to use =SUMIF(B:B;"cost*;C3:C9)+SUMIF(B:B;"cost*;D3:D9)+.. until col G, and so on. Then for the next month I continue.

Is there any easier way to do this? I watch some videos on sumproduct, but it does not seem to work with wildcard, and also it is important that January only sums C:G, Feb sums "Cost*" in H:K an so on.

Lastly this will be used by several people, so I would to just teach them that they can insert a new column and add "cost"+something and it will still add up.

Thanks a lot!

 

[TheJackal]

I think I actually found a formula that solved it:

=SUMPRODUCT((ISNUMBER(SEARCH("red",B5:B10)))*(C5:E10))

Thank you Exceljet!

 

[dreid1011]

Welcome.

Perhaps this:

=SUMPRODUCT((ISNUMBER(SEARCH("Cost",B7:B9)))*(C7:D9))

You will want to exclude the cell that this formula is in from the range, or you'll get a circular reference. So, probably best to start your ranges at the first possible occurrence of "Cost*".

 

[TheJackal]

Thanks a lot dreid1011, awesome!

However, I just found out we will need some text in some of the cells as well (for example the name of the place we rent the equipment from each week), which means sumproduct returns ERROR. Back to the drawing table I guess..

 

[dreid1011]

Are you talking about text in cells between ranges where cost will be? Ex:

Range of B7:B15
B7:B9 contains text with Cost*
B10:B12 contains other text not associated with targeted values
B13:B15 contains text with Cost*

??

 

[dreid1011]

I've come up with this now:

=SUMPRODUCT((ISNUMBER(SEARCH("Cost",B7:B15)))*IF(ISNUMBER(C7:D15),C7:D15,0))

Array formula entered with CTRL+SHIFT+ENTER.

 

[TheJackal]

Are you talking about text in cells between ranges where cost will be? Ex:

Range of B7:B15
B7:B9 contains text with Cost*
B10:B12 contains other text not associated with targeted values
B13:B15 contains text with Cost*

??

Sorry for my late reply. Yes, the thought is that Project 1 has several phases, and we want to keep all things related to project in the rows 4 to 9 etc. So we will have to fill in the of the company providing services in each phase etc in the area C4 to G9.
The other people working in this sheet will probably insert the new rows anywhere, which is why I would the formula to be able to sum the rows 4 to 9.

Find it easier to