# Summing 3rd column numbers based on Wildcard match in column 1 and Exact match in col

1. ## Summing 3rd column numbers based on Wildcard match in column 1 and Exact match in col

Afternoon all,

Sheet1
A:A has dates formatted as *Wednesday, March 14, 2001
B:B has Text values which are any of a number of categories
E:E has a Number value

Sheet 2
Looking to sum all values in 'Sheet1'!E:E That match the wildcard *September* in 'Sheet1'!A:A and exact value "Value" in 'Sheet1'!B:B  Register To Reply

2. ## Re: Summing 3rd column numbers based on Wildcard match in column 1 and Exact match in

Try

=Sumproduct(--(Month(Sheet1!A:A)=9),--(Sheet1!B:B="Value"),Sheet1!E:E)  Register To Reply

3. ## Re: Summing 3rd column numbers based on Wildcard match in column 1 and Exact match in

If the date should be September in any year you could try SUMPRODUCT like this

=SUMPRODUCT((MONTH(A2:A100)=9)*(B2:B100="Value"),E2:E100)

or in Excel 2007 you can use SUMIFS if you want to sum for September 2009,

=SUMIFS(E:E,A:A,">="&DATE(2009,9,1),A:A,"<="&DATE(2009,9,30),B:B,"Value")  Register To Reply

4. ## Re: Summing 3rd column numbers based on Wildcard match in column 1 and Exact match in

...and further to NBVC's SUMPRODUCT if by "Exact" you mean case sensitive (ie Value <> value) then SUMPRODUCT with EXACT should work.
(though I'd add that if you do use SUMPRODUCT I'd personally be inclined to keep ranges as lean as possible - ie avoid entire col. references if possible)  Register To Reply

5. ## Re: Summing 3rd column numbers based on Wildcard match in column 1 and Exact match in

3 great solutions. Thank you very much. I wound up using daddylonglegs Sumifs soultion for 2 reasons.

I was getting errors with the sumproduct and the double --
and
If I ever needed to use the formula with overlapping or smaller date ranges it appeared to be the easiest to modify. eg. summing the numbers for a quarter or 2 week period.

Thanks again!  Register To Reply