How to sumproduct with 2 drivers

[GoKu]

Excel Workbook
	A	B	C	D	E	F	G	H	I	J
9	License No:	Date Tested:	Days left:	Surname:	Initials:	Pf Number:	Shaft:	Section:	Battery Loco:	Diesel Loco:
10	4	9-May-07	-49	Theletsane	TS	A3867344	North	3T	6	6
11	5	11-Jun-08	350	Lekhelebana	M J	M3090971	West	Eng	6	6
12	6	13-Jun-08	352	David	M	Z7588333	West	Jongingozi	*	6
13	7	13-Jun-08	352	Mabusetsa	L	Z0563550	West	Jongingozi	*	6
Data

Hi All, I need to count the number of sixes in 2 collums (I & J) over certain periods of time.

Ive tried the following formula but with no luck:

=SUMPRODUCT(--(Data!B$10:B$60000>=Periods!A5),--(Data!B$10:B$60000<=Periods!B5),--(Data!I$10:J$60000=6))

Any advice please.

 

[Aladin Akyurek]

Control+shift+enter, not just enter...

=SUM(
    IF(Data!B$10:B$60000>=Periods!A5,
    IF(Data!B$10:B$60000<=Periods!B5,
      (Data!I$10:J$60000=6)+0)))

 

[GoKu]

Aladan, ive entered the formula and my result returned 0, which is incorrect.

 

[Aladin Akyurek]

Aladan, ive entered the formula and my result returned 0, which is incorrect.

Did you apply control+shift+enter instead of just enter?

 

[GoKu]

After entering the formula, say into C1, Do I then press Cntr+Shift+Enter??

 

[Aladin Akyurek]

After entering the formula, say into C1, Do I then press Cntr+Shift+Enter??

Yes... While you press the control and shift keys at the same time, hit the enter key.

 

[GoKu]

AHA, yes its working, thank you very much.
This data will change every day through the month, do I need to manually trigger the formula everyday with CTRL+SHIFT+Enter or is it only on the 1st time?

 

[Aladin Akyurek]

AHA, yes its working, thank you very much.
This data will change every day through the month, do I need to manually trigger the formula everyday with CTRL+SHIFT+Enter or is it only on the 1st time?

Only when you need to adjust the ranges...