Is possible conditional sum with from to date and text criteria?

[babaso_tawase]

Hi Every one,
I want your help regarding conditional sum.
I am using EXCEL 2003, that don't have SUMIFS function.
I have data as
Column A= number of sample
Column B= Status C=complete, P=pending
Column C =Date

	A	B	C
1	No. of sample	Status	Date
2	10	P	20/12/17
3	11	P	21/12/17
4	5	C	20/12/17
5	8	C	22/12/17
6	3	P	23/12/17
7	5	C	27/12/17

<tbody>
</tbody>

9 A B C D E 10 NUMBER OF SAMPLE PENDING P = FROM DATE 20/12/17 11 NUMBER OF SAMPLE COMPLETED C = TO DATE 25/12/17 12 <tbody> </tbody>

<tbody>
</tbody>

I want formula to calculate from certain period of date , number of sample completed and number sample pending.

I AM USING THIS FORMULA BUT NOT WORKING
FOR PENDING SAMPLE =SUMPRODUCT((B2:B7=B10)*(E10<=C2:C7<=E11)*A2:A7)
FOR COMPLETED SAMPLE =SUMPRODUCT((B2:B7=B11)*(E10<=C2:C7<=E11)*A2:A7)

Thanks EVERY ONE...

 

[Aladin Akyurek]

Cell C3 contains date 31/11/2017.
I think our date format dd/mm/yyyy is not working in formula, it should be mm/dd/yyyy.If this logic is correct then how to convert dd/mm/yyyy to mm/dd/yyyy so that formula can work.
Thanks

data >> text to columns >> 3rd step choose for MDY

 

[babaso_tawase]

	A	B	C	D	E	F
1	No. of sample	Status	Date
2	10	C	05/11/2017	Stability
3	11	C	31/11/2017	Stability
4	5	C	09/12/2017	Stability
5	8	P	25/12/2017	Routine
6	3	C	12/12/2017	Stability
7	5	C	18/11/2017	Stability
8
9
10	NUMBER OF SAMPLE PENDING	P	=	FROM DATE	05/11/017	Total
11	NUMBER OF SAMPLE COMPLETED	C	=	TO DATE	20/11/2017
12
13
14	Pending	0
15	Completede	23

Spreadsheet Formulas

Cell Formula B14 =IF($F$10="Total",SUMPRODUCT(--($B$2:$B$7=$B$10),--($C$2:$C$7>=$E$10),--($C$2:$C$7<=$E$11),$A$2:$A$7),SUMPRODUCT(--($D$2:$D$7=$F$10),--($B$2:$B$7=$B$10),--($C$2:$C$7>=$E$10),--($C$2:$C$7<=$E$11),$A$2:$A$7)) B15 =IF($F$10="Total",SUMPRODUCT(--($B$2:$B$7=$B$11),--($C$2:$C$7>=$E$10),--($C$2:$C$7<=$E$11),$A$2:$A$7),SUMPRODUCT(--($D$2:$D$7=$F$10),--($B$2:$B$7=$B$11),--($C$2:$C$7>=$E$10),--($C$2:$C$7<=$E$11),$A$2:$A$7))

If in Column A each cell having value 1, I decided not to use column A and remove it as each cell having value 1, then what will be formula based on number of corresponding cell count?

 

[babaso_tawase]

Dear Sir,
Thanks for your answer for Is possible conditional sum with from to date and text criteria? Formula works perfect.
Formula which was suggested by you as below..

A	B	C	D	E	F
1	No. of sample	Status	Date
2	10	C	05-11-17	Stability
3	11	C	31/11/2017	Stability
4	5	C	09-12-17	Stability
5	8	P	25-12-17	Routine
6	3	C	12-12-17	Stability
7	5	C	18-11-17	Stability
8
9
10	NUMBER OF SAMPLE PENDING	P	=	FROM DATE	05/11/017	Total
11	NUMBER OF SAMPLE COMPLETED	C	=	TO DATE	20-11-17
12
13
14	Pending	0
15	Completede	23

Spreadsheet Formulas

Cell Formula B14 =IF($F$10="Total",SUMPRODUCT(--($B$2:$B$7=$B$10),--($C$2:$C$7>=$E$10),--($C$2:$C$7<=$E$11),$A$2:$A$7),SUMPRODUCT(--($D$2:$D$7=$F$10),--($B$2:$B$7=$B$10),--($C$2:$C$7>=$E$10),--($C$2:$C$7<=$E$11),$A$2:$A$7)) B15 =IF($F$10="Total",SUMPRODUCT(--($B$2:$B$7=$B$11),--($C$2:$C$7>=$E$10),--($C$2:$C$7<=$E$11),$A$2:$A$7),SUMPRODUCT(--($D$2:$D$7=$F$10),--($B$2:$B$7=$B$11),--($C$2:$C$7>=$E$10),--($C$2:$C$7<=$E$11),$A$2:$A$7))

I am using above formula as per mrexcel discussion output. Now I don't want now column B, I used single row for every entry, so can I use count cell formula based on date and text as per column D and E?

Thank sir.