Count if, locating cell location.

[sam103329]

Hello

I have had may problems solved by some fantastic users here on Excel forum and I have another thing I am a bit stuck on.

What I wish to do is the following.

I have a column full of data like the below with relevant data on the same row.

I wish to locate the cells which have a specific section most importantly without the "cartridge only" so for example with the below

Word Academy (Nintendo DS)
Word Academy (Nintendo DS) (Cartridge Only)

This would show 2 given there are two with the specific text "Word Academy (Nintendo DS)" there will always be a cell without the (Cartridge Only) part so for this the formula =COUNTIF(D:D,"*" & D1 & "*") seems to do the job to simply count the cells with that in however I do not wish to do this.

What I need to do is locate the cells that have this in but match them together in a large sheet so basically I somehow need to be able to locate the two corresponding cells which both have one of the cells values of "Word Academy (Nintendo DS)"

the cells have data in the row which I need to tie together to create a total value.

For example

Title Qty
Word Academy (Nintendo DS) 5
Word Academy (Nintendo DS) (Cartridge Only) 6

I need to locate the all the cells with "Word Academy (Nintendo DS)" in which would mean just the two cells "Word Academy (Nintendo DS)" + "Word Academy (Nintendo DS) (Cartridge Only)" in the above instance but I need it to then see the cell which has that in its title and add the quantity of the two together leaving me with the below

Title Qty Actual Qty
Word Academy (Nintendo DS) 5 11
Word Academy (Nintendo DS) (Cartridge Only) 6 11

Which is basically just adding together the quantity shown on the spreadsheet in the rows with the containing text "Word Academy (Nintendo DS)" and as "Word Academy (Nintendo DS) (Cartridge Only)"

On the attached spreadsheet Column L is an example of the result I would be desire, I have put a basic =sum formula in to just represent the value it should be showing.

Any questions please let us know and I will get right back

Thanks in advance for any help offered!

[Richard Buttrey]

Hi,

With a helper column M

M2:

Please Login or Register  to view this content.

L2:

Please Login or Register  to view this content.

both copied down.

[wcymiss]

hi sam,
try this in L2 then fill down
=SUMIF($D$2:$D$11,"*"&SUBSTITUTE(D2," (Cartridge Only)","")&"*",$E$2:$E$11)

[sam103329]

To Both above, Perfect solutions

Thank you very much indeed.

As per usual my query has been expertly fixed by 2 people astronomically better at excel than my self

Thank you!!

[arlu1201]

Based on your last post in this thread, its apparent that you are satisfied with the solution(s) you've received and have solved your question, but you haven't marked your thread as "SOLVED". I will do it for you this time.

In future, to mark your thread as Solved, you can do the following -
Select Thread Tools-> Mark thread as Solved.

Incase your issue is not solved, you can undo it as follows -
Select Thread Tools-> Mark thread as Unsolved.

Also, since you are relatively new to the forum, i would like to inform you that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post which helped you. This adds to the reputation of the person who has taken the time to help you.