Using a form of Lookup to find a certain text can be found numerous times then adding a number next to it to the others with the same text.

[CarlyShannan]

Hi,

Trying to find a solution to make this spreadsheet work as easy as possible.

My sheet looks like this.

	A	B	C	D	E	F	G	H	I
1	Order No <tbody> </tbody>	Status <tbody> </tbody>	Order Date <tbody> </tbody>	Order Description <tbody> </tbody>	Type <tbody> </tbody>	Workflow Status <tbody> </tbody>	Supplier <tbody> </tbody>	Delivery Name <tbody> </tbody>	Original Total Amount <tbody> </tbody>
2	11644 <tbody> </tbody>	Open <tbody> </tbody>	6/01/2015 <tbody> </tbody>	CIELO Accessories2 - GC <tbody> </tbody>	Standard <tbody> </tbody>	Payment Due <tbody> </tbody>	Shekou Trading Limited <tbody> </tbody>	Highgrove Trading <tbody> </tbody>	$58,412.30 <tbody> </tbody>
3	11646	Closed <tbody> </tbody>	8/01/2015 <tbody> </tbody>	Rain Party - Showers3 - GC (11526-2) <tbody> </tbody>	Standard <tbody> </tbody>	Paid In Full <tbody> </tbody>	Shekou Trading Limited <tbody> </tbody>	Highgrove Trading <tbody> </tbody>	$41,119.47 <tbody> </tbody>
4	11647 <tbody> </tbody>	Open <tbody> </tbody>	12/01/2015 <tbody> </tbody>	Accessories1 - GC <tbody> </tbody>	Standard <tbody> </tbody>	Proforma Received <tbody> </tbody>	Shekou Trading Limited <tbody> </tbody>	Highgrove Trading <tbody> </tbody>	$88,324.72 <tbody> </tbody>
5	11649 <tbody> </tbody>	Open <tbody> </tbody>	14/01/2015 <tbody> </tbody>	Showers3 - Rain Party - GC <tbody> </tbody>	Standard <tbody> </tbody>	Paid In Full <tbody> </tbody>	Shekou Trading Limited <tbody> </tbody>	Highgrove Trading <tbody> </tbody>	$62,196.90 <tbody> </tbody>

<tbody>
</tbody>

Sorry the table looks weird.

What I need is to look through all of column D for a certain text e.g. Showers3 then find the numbers corresponding in column I, then add them together.

E.G Showers3 is twice on this list D3 & D5 so the formula would add I3 & I5 together. $41119.47 + $62196.90 and in a cell it would show $103316.37.

Please no Macros if possible as I don't understand how to use it.

Thanks in Advance.

Carly

 

[FDibbins]

Try something like this...

=SUMIF(D:D,"Showers3*",I:I)

 

[CarlyShannan]

Unfortunately that hasnt worked it is coming up with a different figure it is coming up with just one figure not both added together.

Also I forgot to mention I need to also sort it by months also sorry big key point I forgot to add.

Thanks.

 

[FDibbins]

oops showers isnt alwats the 1st word. Sorry, try this instead...
=SUMIF(D:D,"*Showers3*",I:I)

 

[CarlyShannan]

oops showers isnt alwats the 1st word. Sorry, try this instead...
=SUMIF(D:D,"*Showers3*",I:I)

Hey Ford,

Thanks that work perfectly Is there anyway to incorporate sorting out by certain month?

If you know you would be a life saver!

Thanks

 

[FDibbins]

=SUMIF(I:I,D:D"*Showers3*",C:C,$J$1)
where J1 contains your date.

You could create a small table with all your dates below each other, then use that formula copied down, with 1 small modification...
=SUMIF(I:I,D:D"*Showers3*",C:C,$J1)

 

[CarlyShannan]

I tried that its hasn't worked keeps saying there is a problem with the formula ( I had to change the $J$1 to $J$2 to chose the right date) it thinks I'm not trying to type a formula.

I might explain it better this way, what I want the formula to pretty much do it is this:

If Column D contains Showers3 & was ordered in January 2015 (Column C) (also not the date in the month just if it occurs in the month) then add their corresponding numbers together.

So January figure for Showers3 will be:

$41,119.47

<tbody>
</tbody>

& May's figure for Showers3 will be:

$73,683.70

<colgroup><col width="145"></colgroup><tbody>
</tbody>

Hope this makes a little more sense.

Thanks

 

[FDibbins]

grrr my bad...

=SUMIFs(I:I,D:D"*Showers3*",C:C,$J1)

 

[CarlyShannan]

I added a comma in after D:D and it seemed to accept that it is a formula but its coming up with a 0 now not $41,119.47.

I have in the J Column 01-15 through to 12-15 is this right?

 

[FDibbins]

Apologies again, yes, I left out the comma...
=SUMIFs(I:I,D:D,"*Showers3*",C:C,$J1)

I have in the J Column 01-15 through to 12-15 is this right?

No, that date needs to be a real date, in the format of d/m/y