sumif

[tpkelley_no]

I would like to use sumif to sum a total. In cell “B47” they have the following number ”102”. In column “C9” and going down (a row will be added each week). They have the following information:

102/01-106 500.00

106/01-106 300.00

102/01-106 350.00

The total should be 850.00

The number I need to be total are in column “D”

 

[ken2step]

So you want to search the cell contents for the first 3 characters for "102", then search the last 6 characters to get the numbers to total? Do I understand you correctly?

 

[tpkelley_no]

yes, search the first three in column "C" starting with cell "C9". anything past the first three are useless, and total amount I am looking for is in column "D" starting with "D9"

 

[kweaver]

Is a helper column OK? If so, what about something like this? Put the SUM formula where you need it.

Book1
	B	C	D
9	850	102/01-106 500.00	500
10		106/01-106 300.00	0
11		102/01-106 350.00	350
Sheet1

Cell Formulas
Range	Formula
B9	B9	=SUM(D:D)
D9:D11	D9	=IF("102"=LEFT(C9,3),--RIGHT(C9,LEN(C9)-FIND(" ",C9)),0)

 

[Fluff]

Another option

=SUMIFS(D:D,C:C,"102*")

 

[ken2step]

Another option

=SUMIFS(D:D,C:C,"102*")

Sumifs........Sweet....didn't know about that one! Thanks Fluff!

=SUMIFS(D:D,C:C,CONCATENATE(B47,"*"))

Then it's dynamic

 

[Fluff]

You don't need the concatenate function, you can just use

=SUMIFS(D:D,C:C,B47&"*"))

 

[ken2step]

Didn't know that would work in a formula......as always I learn so much here!
Thanks again Fluff!

 

[tpkelley_no]

it works TY

 

[Fluff]

Glad we could help & thanks for the feedback.