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

#### Kidmon

##### Board Regular
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

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},\$Z\$1:\$Z\$99,\$Y\$1:\$Y\$99),2,False) to lookup Y values to left of Z values.
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)

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``````

#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.

Ecxel 2010
Thax again,
What am I doing wrong in this formula?
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 is extremely valuable to those of us who are Excel Challenged.
Kidmon

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")

WOW, How could I have been so close yet so far. That was it. Workin great now.
THANK YOU

Replies
9
Views
159
Replies
3
Views
110
Replies
14
Views
217
Replies
12
Views
686
Replies
21
Views
682

1,219,917
Messages
6,150,946
Members
450,996
Latest member
darko1515s

### 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.

### Which adblocker are you using?

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

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