try now
=SUM(IF(Entries!$A$3:Entries!$A$1000 >= DATE(2015,COLUMN()-1,1),1,0)*IF(Entries!$A$3:Entries!$A$1000 < DATE(2015,COLUMN(),1),1,0)*IF(Entries!$C$3:Entries!$C$1000 = $A2,1,0)*IF(Entries!$D$3:Entries!$D$1000 > 0, Entries!$D$3:Entries!$D$1000, Entries!$E$3:Entries!$E$1000))
The formula has to be array entered Ctrl+Shift+Enter, then Excel adds {} brackets to show that it is an array formula.=IF(SUMPRODUCT((Entries!$A$3:Entries!$A$1000 >= DATE(2015,COLUMN()-1,1))*(Entries!$A$3:Entries!$A$1000 < DATE(2015,COLUMN(),1))*(Entries!$C$3:Entries!$C$1000 = $A2)*Entries!$D$3:Entries!$D$1000)>0,SUMPRODUCT((Entries!$A$3:Entries!$A$1000 >= DATE(2015,COLUMN()-1,1))*(Entries!$A$3:Entries!$A$1000 < DATE(2015,COLUMN(),1))*(Entries!$C$3:Entries!$C$1000 = $A2)*Entries!$D$3:Entries!$D$1000),SUMPRODUCT((Entries!$A$3:Entries!$A$1000 >= DATE(2015,COLUMN()-1,1))*(Entries!$A$3:Entries!$A$1000 < DATE(2015,COLUMN(),1))*(Entries!$C$3:Entries!$C$1000 = $A2)*Entries!$E$3:Entries!$E$1000))
=ARRAY_CONSTRAIN(ARRAYFORMULA(SUM(IF(Entries!$A$3:'Entries'!$A$1000 >= DATE(2015,COLUMN()-1,1),1,0)*IF(Entries!$A$3:'Entries'!$A$1000 < DATE(2015,COLUMN(),1),1,0)*IF(Entries!$C$3:'Entries'!$C$1000 = $A2,1,0)*IF(Entries!$D$3:'Entries'!$D$1000 > 0, Entries!$D$3:'Entries'!$D$1000, Entries!$E$3:'Entries'!$E$1000))), 1, 1)
=IF(SUMPRODUCT((Entries!$A$3:Entries!$A$1000 >= DATE(2015,COLUMN()-1,1))*(Entries!$A$3:Entries!$A$1000 < DATE(2015,COLUMN(),1))*(Entries!$C$3:Entries!$C$1000 = $A2)*Entries!$D$3:Entries!$D$1000)>0,SUMPRODUCT((Entries!$A$3:Entries!$A$1000 >= DATE(2015,COLUMN()-1,1))*(Entries!$A$3:Entries!$A$1000 < DATE(2015,COLUMN(),1))*(Entries!$C$3:Entries!$C$1000 = $A2)*Entries!$D$3:Entries!$D$1000),SUMPRODUCT((Entries!$A$3:Entries!$A$1000 >= DATE(2015,COLUMN()-1,1))*(Entries!$A$3:Entries!$A$1000 < DATE(2015,COLUMN(),1))*(Entries!$C$3:Entries!$C$1000 = $A2)*Entries!$E$3:Entries!$E$1000))
=IF(SUMPRODUCT(--(Entries!$A$3:Entries!$A$1000 >= DATE(2015,COLUMN()-1,1)),--(Entries!$A$3:Entries!$A$1000 < DATE(2015,COLUMN(),1)),--(Entries!$C$3:Entries!$C$1000 = $A2),Entries!$D$3:Entries!$D$1000)>0,SUMPRODUCT(--(Entries!$A$3:Entries!$A$1000 >= DATE(2015,COLUMN()-1,1)),--(Entries!$A$3:Entries!$A$1000 < DATE(2015,COLUMN(),1)),--(Entries!$C$3:Entries!$C$1000 = $A2),Entries!$D$3:Entries!$D$1000),SUMPRODUCT(--(Entries!$A$3:Entries!$A$1000 >= DATE(2015,COLUMN()-1,1)),--(Entries!$A$3:Entries!$A$1000 < DATE(2015,COLUMN(),1)),--(Entries!$C$3:Entries!$C$1000 = $A2),Entries!$E$3:Entries!$E$1000))
=IF(AND(SUMPRODUCT(Entries!A3:A1000 >= DATE(2015;COLUMN()-1;1); Entries!A3:A1000 < DATE(2015;COLUMN();1); Entries!C3:C1000 = A3));"passed";"failed")
=IF(SUMPRODUCT((Entries!$A$3:$A$1000 >= DATE(2015;COLUMN()-1;1)) * (Entries!$A$3:$A$1000 < DATE(2015;COLUMN();1)) * (Entries!$C$3:$C$1000 = $A3) * (Entries!$D$3:$D$1000 > 0));SUMPRODUCT((Entries!$A$3:$A$1000 >= DATE(2015;COLUMN()-1;1)) * (Entries!$A$3:$A$1000 < DATE(2015;COLUMN();1))*(Entries!$C$3:$C$1000 = $A3) * (Entries!$D$3:$D$1000 > 0));SUMPRODUCT((Entries!$A$3:$A$1000 >= DATE(2015;COLUMN()-1;1)) * (Entries!$A$3:$A$1000 < DATE(2015;COLUMN();1)) *(Entries!$C$3:$C$1000 = $A3) * (Entries!$D$3:$D$1000 > 0)))
=IF(SUMPRODUCT(--(Entries!$A$3:$A$1000 >= DATE(2015,COLUMN()-1,1)), --(Entries!$A$3:$A$1000 < DATE(2015,COLUMN(),1)), --(Entries!$C$3:$C$1000 = $A2),Entries!$D$3:$D$1000),SUMPRODUCT(--(Entries!$A$3:$A$1000 >= DATE(2015,COLUMN()-1,1)), --(Entries!$A$3:$A$1000 < DATE(2015,COLUMN(),1)), --(Entries!$C$3:$C$1000 = $A2),Entries!$D$3:$D$1000),SUMPRODUCT(--(Entries!$A$3:$A$1000 >= DATE(2015,COLUMN()-1,1)), --(Entries!$A$3:$A$1000 < DATE(2015,COLUMN(),1)), --(Entries!$C$3:$C$1000 = $A2),Entries!$E$3:$E$1000))
=ARRAY_CONSTRAIN(ARRAYFORMULA(SUM(IF(Entries!$A$3:$A$1000 >= DATE(2015,COLUMN()-1,1),1,0)*IF(Entries!$A$3:$A$1000 < DATE(2015,COLUMN(),1),1,0)*IF(Entries!$C$3:$C$1000 = $A2,1,0)*IF(Entries!$D$3:$D$1000 > 0, Entries!$D$3:$D$1000, Entries!$E$3:$E$1000))), 1, 1)
=IF(
SUMPRODUCT((Entries!$A$3:$A$1000 >= DATE(2015;COLUMN()-1;1)) * (Entries!$A$3:$A$1000 < DATE(2015;COLUMN();1))) * (Entries!$C$3:$C$1000 = A3) * Entries!$D$3:$D$1000;
SUMPRODUCT((Entries!$A$3:$A$1000 >= DATE(2015;COLUMN()-1;1)) * (Entries!$A$3:$A$1000 < DATE(2015;COLUMN();1)) * (Entries!$C$3:$C$1000 = A3) * Entries!$D$3:$D$1000);
SUMPRODUCT((Entries!$A$3:$A$1000 >= DATE(2015;COLUMN()-1;1)) * (Entries!$A$3:$A$1000 < DATE(2015;COLUMN();1)) * (Entries!$C$3:$C$1000 = A3) * Entries!$E$3:$E$1000))
If you are experiencing a similar issue, please ask a related question
Title | # Comments | Views | Activity |
---|---|---|---|
MIgrating Office Settings 2010>365 | 4 | 30 | |
VBA Hide Row Based on Cell Color | 19 | 174 | |
How can I integrate PostgreSQL database in google docs spreadsheet? | 1 | 309 | |
Calculating Percentile Value inside Excel. | 2 | 61 |
Join the community of 500,000 technology professionals and ask your questions.
Connect with top rated Experts
22 Experts available now in Live!