Hmmm, this is what I see in my test document (I'm only using 10 rows instead of 100):=SUMPRODUCT((MONTH(A1:A100)=1)*((Q1:Q100)="WON")*B1:B100)
tried this and all I get is #VALUE! back?
Excel 2010 | |||||||
---|---|---|---|---|---|---|---|
A | B | C | D | E | |||
1 | 01/01/2016 | 10 | WON | =SUMPRODUCT((MONTH(A1:A10)=1)*((C1:C10)="WON")*B1:B10) ---> | 40 | ||
2 | 02/01/2016 | 10 | |||||
3 | 03/01/2016 | 10 | |||||
4 | 04/01/2015 | 10 | WON | ||||
5 | 05/01/2016 | 10 | |||||
6 | 06/01/2016 | 10 | |||||
7 | 07/01/2014 | 10 | WON | ||||
8 | 08/01/2016 | 10 | |||||
9 | 09/01/2016 | 10 | |||||
10 | 10/01/2016 | 10 | WON | ||||
Sheet1 |
worked out what the issue is, I have a few empty cells in my date column and a few question marks etc, the formula works fine when the data is all there..