SUMPRODUCT help - basic

[krispatterson]

Hi guys

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

Data:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]DATES[/TD]
[TD]NAMES[/TD]
[TD]£ FIGURES[/TD]
[TD][/TD]
[TD]01/11/16[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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