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.

C

CarlyShannan

New Member
Joined
Apr 7, 2015
Messages
7
Hi,

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

My sheet looks like this.

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl67, width: 64"]Order No[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 60"]
<tbody>[TR]
[TD="class: xl67, width: 60"]Status[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 75"]
<tbody>[TR]
[TD="class: xl67, width: 75"]Order Date[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 257"]
<tbody>[TR]
[TD="class: xl67, width: 257"]Order Description[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 62"]
<tbody>[TR]
[TD="class: xl67, width: 62"]Type[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 150"]
<tbody>[TR]
[TD="class: xl67, width: 150"]Workflow Status[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 210"]
<tbody>[TR]
[TD="class: xl67, width: 210"]Supplier[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 121"]
<tbody>[TR]
[TD="class: xl67, width: 121"]Delivery Name[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 145"]
<tbody>[TR]
[TD="class: xl67, width: 145"]Original Total Amount[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]

<tbody>
[TD="align: right"]11644[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]

</tbody>
[/TD]
[TD][TABLE="width: 60"]
<tbody>[TR]
[TD="width: 60"]Open[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 75"]
<tbody>[TR]
[TD="class: xl67, width: 75, align: right"]6/01/2015[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 257"]
<tbody>[TR]
[TD="width: 257"]CIELO Accessories2 - GC[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 62"]
<tbody>[TR]
[TD="width: 62"]Standard[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 150"]
<tbody>[TR]
[TD="width: 150"]Payment Due[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 210"]
<tbody>[TR]
[TD="width: 210"]Shekou Trading Limited[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 121"]
<tbody>[TR]
[TD="width: 121"]Highgrove Trading[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 145"]
<tbody>[TR]
[TD="class: xl67, width: 145, align: right"]$58,412.30[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]11646[/TD]
[TD][TABLE="width: 60"]
<tbody>[TR]
[TD="width: 60"]Closed[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 75"]
<tbody>[TR]
[TD="class: xl67, width: 75, align: right"]8/01/2015[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 257"]
<tbody>[TR]
[TD="width: 257"]Rain Party - Showers3 - GC (11526-2)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 62"]
<tbody>[TR]
[TD="width: 62"]Standard[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 150"]
<tbody>[TR]
[TD="width: 150"]Paid In Full[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 210"]
<tbody>[TR]
[TD="width: 210"]Shekou Trading Limited[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 121"]
<tbody>[TR]
[TD="width: 121"]Highgrove Trading[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 145"]
<tbody>[TR]
[TD="class: xl67, width: 145, align: right"]$41,119.47[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="align: right"]11647[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 60"]
<tbody>[TR]
[TD="width: 60"]Open[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 75"]
<tbody>[TR]
[TD="class: xl67, width: 75, align: right"]12/01/2015[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 257"]
<tbody>[TR]
[TD="width: 257"]Accessories1 - GC[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 62"]
<tbody>[TR]
[TD="width: 62"]Standard[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 150"]
<tbody>[TR]
[TD="width: 150"]Proforma Received[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 210"]
<tbody>[TR]
[TD="width: 210"]Shekou Trading Limited[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 121"]
<tbody>[TR]
[TD="width: 121"]Highgrove Trading[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 145"]
<tbody>[TR]
[TD="class: xl67, width: 145, align: right"]$88,324.72[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]

<tbody>
[TD="align: right"]11649[/TD]

</tbody>
[/TD]
[TD][TABLE="width: 60"]
<tbody>[TR]
[TD="width: 60"]Open[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 75"]
<tbody>[TR]
[TD="class: xl67, width: 75, align: right"]14/01/2015[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 257"]
<tbody>[TR]
[TD="width: 257"]Showers3 - Rain Party - GC[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 62"]
<tbody>[TR]
[TD="width: 62"]Standard[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 150"]
<tbody>[TR]
[TD="width: 150"]Paid In Full[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 210"]
<tbody>[TR]
[TD="width: 210"]Shekou Trading Limited[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 121"]
<tbody>[TR]
[TD="width: 121"]Highgrove Trading[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 145"]
<tbody>[TR]
[TD="class: xl67, width: 145, align: right"]$62,196.90[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

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
 
Sort by date Sort by votes
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.
 
Upvote 0
=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)
 
Upvote 0
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:

[TABLE="width: 145"]
<tbody>[TR]
[TD="class: xl67, width: 145, align: right"]$41,119.47
[/TD]
[/TR]
</tbody>[/TABLE]
& May's figure for Showers3 will be:

[TABLE="width: 145"]
<colgroup><col width="145"></colgroup><tbody>[TR]
[TD="class: xl67, width: 145, align: right"]$73,683.70[/TD]
[/TR]
</tbody>[/TABLE]


Hope this makes a little more sense.

Thanks
 
Upvote 0
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?
 
Upvote 0
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?
Click to expand...

No, that date needs to be a real date, in the format of d/m/y
 
Upvote 0
You must log in or register to reply here.

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top