formula to search text from another sheet and add the sum based on a date range

[Billyngu]

Hi All,

I'm trying to create a formula that will search for a specific text in "sheet1" within a date range and then SUM the total on "sheet2". I've manage to use the SUMPRODUCT (below) formula but want to expand on this so the SUM can be shown on another sheet(summary page). =SUMPRODUCT((ISNUMBER(FIND({"nmlclex06"},A1:A10)))*(B1:B10)) In this example I search for the text "nmlclex06" and SUM the size, but don't have a date range and don't know how to SUM the data on another sheet. Can someone please assist?



Columns: Name, Size, Date
nmlclex06 1 14/12/2013
nmlclap12 10 12/12/2013
nmlclex06 5 1/10/2013
nmlclex06 1 9/10/2013
nmlclex06 1 1/01/2014
nmlclap12 1 24/10/2013
nmlclex06 1 13/11/2013
nmlclap12 1 18/12/2013
nmlclap12 1 9/12/2013
nmlclap12 1 30/12/2013

Thanks.

[:) Sixthsense :)]

Hi Billyngu,

Welcome to the forum

Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[Billyngu]

Hi Six,

Thanks for the response. I've attached a workbook sample with comments. The workbook has 3 sheets; data, requirement(before), intended result(after).

Thanks.

[:) Sixthsense :)]

In B2 Cell of Summary sheet

=SUMIFS(Table1[[#All],[Size]],Table1[[#All],[Name]],"*"&$A2&"*",Table1[[#All],[Date]],">="&VALUE(1&"-"&SUBSTITUTE(TRIM(REPLACE(B$1,1,SEARCH("For",B$1)+3,""))," ","-")),Table1[[#All],[Date]],"<="&EOMONTH(VALUE(1&"-"&SUBSTITUTE(TRIM(REPLACE(B$1,1,SEARCH("For",B$1)+3,""))," ","-")),0))

Drag it down and right....

[FDibbins]

here is another option.

1st, change the headings in the summary table to actual dates (10/1/2013, 11/1/2013 etc). Then use this, copied across and down...

=SUMIFS(Data!$C:$C,Data!$A:$A,'Summary(before)'!$A2,Data!$B:$B,">="&B$1,Data!$B:$B,"<"&EDATE(B$1,1))

edit: @ Six, did I meantion I hate how excel structures the references in a table? lol

[Billyngu]

Hi Six,

Thanks for that. The sum value from the formula to what is expected doesn't seem to match. I verified this by using the filter. On the Data sheet, If I do a manual filter on "Name:nmlclex03" and date range for 1/10/2013 to 31/10/2013 and SUM up , the value is not the same. Am i missing something?

Thanks.

[Billyngu]

HI Fdibbins,

I like you alternative, thank you, but the value still doesn't seem to match the filtered SUM as per my previous msg to Six. Any ideas?

[FDibbins]

Im not sure which answers you are looking at...

	A	B	C	D	E	F	G
1	Name	1-Oct-2013	1-Nov-2013	1-Dec-2013	1-Jan-2014
2	nmlclex03	76795.575	68241.646	79930.322	75581.135		Expected results: B2 =76795.575, C2=68241.646

[:) Sixthsense :)]

@ Six, did I meantion I hate how excel structures the references in a table? lol

I also felt uncomfortable, the way excel creates references to table but any how I have the habit of hitting Ctrl+Spacebar for selecting the whole column references which automatically created the table references instead of whole column references

[XOR LX]

Agreed, my friends!

Structured References are one of the few 'improvements' in later versions of Excel that I think are simply more hassle than they're worth, and I personally avoid them like the plague!

[Billyngu]

Thanks Fdibbins. I had the dates set incorrectly.

Cheers!

[FDibbins]

you'r welcome. If this answered your question, please take a moment to mark the thread as "solved" - it helps keep things neat and tidy lol, and consider adding the reputation to those that helped (see points 2 & 3 below my 1st post to you)