Search column A and then SUM no. value attached in Column E

Kidmon

Board Regular
Joined
Mar 4, 2011
Messages
71
Using Excel 2010 Home Edition:
I need to Search Column A for specific "Text" and then add the number value that is attached in column E. All this needs to be done within a Specific date range.
Love this forum, You guys are awesome.
Thanx
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

T. Valko

Well-known Member
Joined
May 9, 2009
Messages
16,623
Using Excel 2010 Home Edition:
I need to Search Column A for specific "Text" and then add the number value that is attached in column E. All this needs to be done within a Specific date range.
Love this forum, You guys are awesome.
Thanx
You didn't say where the date range was...I'll assume it's column B.

Use cells to hold the criteria:
  • G2 = the text criteria
  • H2 = lower date boundary like 1/1/2011
  • I2 = upper date boundary like 1/15/2011
Then:

=SUMIFS(E2:E100,A2:A100,G2,B2:B100,">="&H2,B2:B100,"<="&I2)
 

pedie

Well-known Member
Joined
Apr 28, 2010
Messages
3,875
Perhaps, you may try something like this...

Code:
[/FONT]
[FONT=Courier New]Sub tryMe()
Dim foundtext As Range
Dim x As String[/FONT]
[FONT=Courier New]x = InputBox("Please input the search number...", "", "")
        Set foundtext = Range("A1:A10").Find(What:=x, After:=ActiveCell, LookIn:=xlValues, _
        LookAt:=xlWhole)
        
    If foundtext Is Nothing Then
    MsgBox "Nothing found"
    Else
    MsgBox foundtext + Cells(Selection.Row, "E")
    End If
End Sub
 

Kidmon

Board Regular
Joined
Mar 4, 2011
Messages
71
#Name? I think I see you thinking However,I am unsure how to enter the formula if my date range is from sheet 1 but my summary and formula is on sheet 2. Placing a date reference cell on the same page is questionable. Can it be hidden? My page is already pretty full.

=SUMIFS(E2:E100,A2:A100,G2,B2:B100,">="&H2,B2:B100,"<="&I2)
I need to reference this info on sheet 1 from sheet 2.

Thanx for your help again.
 

Kidmon

Board Regular
Joined
Mar 4, 2011
Messages
71

ADVERTISEMENT

Ecxel 2010
Thax again,
What am I doing wrong in this formula? :confused:
MY error message for $G:$G is invalid.

Sheet 1 $D = Specific "Text" to locate
Sheet 1 $E = a number value I need to add
Sheet 1 $G = a date range to search in

=SUMIF('Sheet1'!$D:$D,"1:1 Volunteer (Men)",'Sheet1'!$E:$E,'Sheet1'!$G:$G,'Sheet1'!,">="&"1/1/2011"+0,,"<="&"1/31/2011"+0)

Thanx for the help this forum :biggrin:is extremely valuable to those of us who are Excel Challenged.
Kidmon
 

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
I think you mean SUMIFS (with an "S" at the end) in which case the sum range comes first. Try

=SUMIFS('Sheet1'!$E:$E,'Sheet1'!$D:$D,"1:1 Volunteer (Men)",'Sheet1'!$G:$G,">=1/1/2011",'Sheet1'!$G:$G,"<=1/31/2011")
 

Kidmon

Board Regular
Joined
Mar 4, 2011
Messages
71
WOW, How could I have been so close yet so far. That was it. Workin great now.
THANK YOU
 

Forum statistics

Threads
1,141,016
Messages
5,703,748
Members
421,312
Latest member
Mooncake1

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
Top