SUMPRODUCT help - basic

[krispatterson]

Hi guys

Struggling with what I think should be fairly easy to resolve... Advices please!

Data:

	A	B	C	D	E	F
1	DATES	NAMES	£ FIGURES		01/11/16
2

<tbody>
</tbody>

The formula I want in F1:

> looks for all dates in Column A from E1 + 12m (so, 01/11/16 to 01/11/17)
> looks for a specific name in Column B (ie, John)
> adds up the figures based on that criteria.


What I tried was:
=SUMPRODUCT(--(YEAR($A$3:$A$20000)=YEAR($E$1)*($B$3:$B$20000="*john*")*$C$3:$C$20000))


But that's returning a '0' result in all circumstances - I've obviously got it wrong!

Any advice welcomed please

Thanks

 

[Jonmo1]

2 probems.

1. Misplaced )
=SUMPRODUCT(--(YEAR($A$3:$A$20000)=YEAR($E$1)*($B$3:$B$20000="*john*")*$C$3:$C$20000))
shoul be
=SUMPRODUCT(--(YEAR($A$3:$A$20000)=YEAR($E$1))*($B$3:$B$20000="*john*")*$C$3:$C$20000)

2. Wildcards don't work in sumproduct ($B$3:$B$20000="*john*")
That will literally look for the asterisks in the string of column B.
You'd have to do
(ISNUMBER(SEARCH("john",$B$3:$B$20000)))

 

[Aladin Akyurek]

Does this succeed?

=SUMIFS(C:C,A:A,">="&E1,A:A,"<="&EDATE(E1,12),B:B,"John")

 

[krispatterson]

Thanks both - but no longer need this as I've been made redundant from the job I needed it for!!

Never mind, onwards and upwards