Calculate sum based on partial text match and latest date

**moondog1023** · 06-19-2014, 09:00 AM

Hi,

I need a formula to do a partial text match on column B to find all rows that contain "825-CL-A", then sum column C for all applicable rows with the latest date. In this example the result should be "4.25 + 6.50 = 10.75". I'm using Excel 2003 for this project. Thanks for in advance for any help.

A B C
7/1/2012 0:00 825-CL-A-41091-REG 4.00
7/1/2012 0:00 825-CL-A-41091-REG 6.25
7/1/2013 0:00 825-CL-A-41456-REG 4.25
7/1/2013 0:00 825-CL-A-41456-REG 6.50
1/1/2014 0:00 825-CL-A-41640-REG 4.25
1/1/2014 0:00 825-CL-A-41640-REG 6.50
3/1/2014 0:00 825-CL-E-41699-REG 3.00
3/1/2014 0:00 825-CL-E-41699-REG 4.00

**ragulduy** · 06-19-2014, 09:11 AM

Hi - please see example attached

**sktneer** · 06-19-2014, 09:30 AM

Try this regular formula

Please Login or Register  to view this content.

**ragulduy** · 06-19-2014, 09:52 AM

@sktneer,

I originally tried something along those lines but found It gave the wrong answer, if, for example, I changed A7 in your example to 01/01/2014

Maybe this for a non-array version?
=SUMPRODUCT(--(A1:A8=MAX(INDEX(ISNUMBER(SEARCH("825-CL-A",B1:B8))*A1:A8,0))),--ISNUMBER(SEARCH("825-CL-A",B1:B8)),C1:C8)

**moondog1023** · 06-19-2014, 09:55 AM

Thanks for your quick response. When I tried your formula I realized an oversight on my part. The summed total was too high, I was getting 26.88 instead of 10.75. I discovered two additional rows that were meeting my search criteria but should not have been included, so I need to refine the partial search criteria more closely. I need to find the rows that start with the text string "825-CL-A" in column B but also end with text string "REG" in column B.

A B C
7/1/2012 0:00 825-CL-A-41091-REG 4.00
7/1/2012 0:00 825-CL-A-41091-REG 6.25
7/1/2013 0:00 825-CL-A-41456-REG 4.25
7/1/2013 0:00 825-CL-A-41456-REG 6.50
1/1/2014 0:00 825-CL-A-41640-REG 4.25
1/1/2014 0:00 825-CL-A-41640-REG 6.50
1/1/2014 0:00 825-CL-A-41640-OT 6.38
1/1/2014 0:00 825-CL-A-41640-OT 9.75
3/1/2014 0:00 825-CL-E-41699-REG 3.00
3/1/2014 0:00 825-CL-E-41699-REG 4.00

**ragulduy** · 06-19-2014, 10:00 AM

=SUMPRODUCT(IF(A1:A8=MAX(IF(LEFT(B1:B8,8)="825-CL-A",A1:A8,0)*IF(RIGHT(B1:B8,3)="REG",1,0)),C1:C8,0),(--NOT(ISERROR(SEARCH("825-CL-A",B1:B8)))))

as an array formula (confirm with ctrl+shift+enter) to replace the formula in E1 in my example attached to post #2

**sktneer** · 06-19-2014, 10:02 AM

@ragulduy
Very good point. I think in that case the formula may be like this (apart from one you suggested in your last reply)

Please Login or Register  to view this content.

Is that ok?

Or simply this..

Please Login or Register  to view this content.

**Tony Valko** · 06-19-2014, 10:19 AM

One way...

Data Range

	A	B	C	D	E
1	Date	Item	Value
2	7/1/2012 0:00	825-CL-A-41091-REG	4	------	825-CL-A
3	7/1/2012 0:00	825-CL-A-41091-REG	6.25		10.75
4	7/1/2013 0:00	825-CL-A-41456-REG	4.25
5	7/1/2013 0:00	825-CL-A-41456-REG	6.5
6	1/1/2014 0:00	825-CL-A-41640-REG	4.25
7	1/1/2014 0:00	825-CL-A-41640-REG	6.5
8	3/1/2014 0:00	825-CL-E-41699-REG	3
9	3/1/2014 0:00	825-CL-E-41699-REG	4

This array formula** entered in E3:

=SUM(IF(A2:A9=MAX(IF(LEFT(B2:B9,8)=E2,A2:A9)),C2:C9))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

**ragulduy** · 06-19-2014, 10:26 AM

@Tony, I think that has the same problem I mentioned in post #4.

**sktneer** · 06-19-2014, 10:29 AM

Originally Posted by Tony Valko

=SUM(IF(A2:A9=MAX(IF(LEFT(B2:B9,8)=E2,A2:A9)),C2:C9))

This formula will also give wrong output as pointed out by ragulduy in post#4.

**Tony Valko** · 06-19-2014, 10:34 AM

Originally Posted by sktneer

This formula will also give wrong output as pointed out by ragulduy in post#4.

I see.

In that case try this array formula**:

=SUM(IF(A2:A9=MAX(IF(LEFT(B2:B9,8)=E2,A2:A9)),IF(LEFT(B2:B9,8)=E2,C2:C9)))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

**sktneer** · 06-19-2014, 10:34 AM

Try this....

Please Login or Register  to view this content.

**Tony Valko** · 06-19-2014, 10:39 AM

Originally Posted by moondog1023

but also end with text string "REG" in column B.

Ooops!

I missed that part.

Disregard all of my replies.

I'll just move along!

**martindwilson** · 06-19-2014, 10:51 AM

you can use wildcard in the search to reduce that a bit
=SUMPRODUCT(--(ISNUMBER(SEARCH("825-CL-A*REG",B2:B20)))*(A2:A20=MAX(INDEX(ISNUMBER(SEARCH("825-CL-A",B2:B20))*A2:A20,0))),C2:C20)

**Tony Valko** · 06-19-2014, 10:56 AM

Originally Posted by martindwilson

you can use wildcard in the search to reduce that a bit
=SUMPRODUCT(--(ISNUMBER(SEARCH("825-CL-A*REG",B2:B20)))*(A2:A20=MAX(INDEX(ISNUMBER(SEARCH("825-CL-A",B2:B20))*A2:A20,0))),C2:C20)

I think you'd have to include the *REG in the MAX(Search as well.

**Tony Valko** · 06-19-2014, 11:00 AM

Or, maybe not!

**moondog1023** · 06-19-2014, 01:59 PM

I tried ragulduy's second formula and again the resulting summed total is too high. I've attached an extract directly from my source data. The formula and result is in cell I1. I've highlighted the correct target rows which should sum to 29.33, not 157.49 as its currently doing.

Example.xls

**Tony Valko** · 06-19-2014, 02:16 PM

Martin Wilson's formula in post #14 will do what you want.

You just need to use the correct ranges.

**sktneer** · 06-19-2014, 03:02 PM

Did you try the formula suggested in post#12 ? I think you didn't.
Try this.....

Please Login or Register  to view this content.

See the attached sheet in which I have applied this formula in K1. Is it the desired output what you are expecting the formula to return?

**sktneer** · 06-19-2014, 03:04 PM

Or alternatively you may try this also...

Please Login or Register  to view this content.

**moondog1023** · 06-20-2014, 03:16 PM

Several of these formulas worked in solving this problem, thanks again for all your help!

**Tony Valko** · 06-20-2014, 04:02 PM

Good deal. Thanks for the feedback!

If your question has been solved please mark the thread as being solved.

In the menu bar above the very first post select Thread Tools, then select Mark this thread as solved.

Calculate sum based on partial text match and latest date

LinkBack

Thread Tools

Rate This Thread

Display

Calculate sum based on partial text match and latest date

Re: Calculate sum based on partial text match and latest date

Re: Calculate sum based on partial text match and latest date

Re: Calculate sum based on partial text match and latest date

Re: Calculate sum based on partial text match and latest date

Re: Calculate sum based on partial text match and latest date

Re: Calculate sum based on partial text match and latest date

Re: Calculate sum based on partial text match and latest date

Re: Calculate sum based on partial text match and latest date

Re: Calculate sum based on partial text match and latest date

Re: Calculate sum based on partial text match and latest date

Re: Calculate sum based on partial text match and latest date

Re: Calculate sum based on partial text match and latest date

Re: Calculate sum based on partial text match and latest date

Re: Calculate sum based on partial text match and latest date

Re: Calculate sum based on partial text match and latest date

Re: Calculate sum based on partial text match and latest date

Re: Calculate sum based on partial text match and latest date

Re: Calculate sum based on partial text match and latest date

Re: Calculate sum based on partial text match and latest date

Re: Calculate sum based on partial text match and latest date

Re: Calculate sum based on partial text match and latest date

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Match partial text when partial text is not exact

Search and copy based on partial text match

Powerpivot - Returning latest update based on latest date

return a text based on latest date and another criteria

remove duplicates based on partial text match

Bookmarks

Bookmarks

Posting Permissions