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

[TABLE="width: 500"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]No. of sample[/TD]
[TD="align: center"]Status[/TD]
[TD="align: center"]Date[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]P[/TD]
[TD="align: center"]20/12/17[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]11[/TD]
[TD="align: center"]P[/TD]
[TD="align: center"]21/12/17[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]20/12/17[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]8[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]22/12/17[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]P[/TD]
[TD="align: center"]23/12/17[/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]27/12/17[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="class: cms_table_cms_table, width: 500"]
<tbody>[TR]
[/TR]
[TR]
[TD][TABLE="width: 500"]
<tbody>[TR]
[TD="align: center"]9[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[/TR]
[TR]
[TD="align: center"]10[/TD]
[TD="align: center"]NUMBER OF SAMPLE PENDING[/TD]
[TD="align: center"]P[/TD]
[TD="align: center"]=[/TD]
[TD="align: center"]FROM DATE[/TD]
[TD="align: center"]20/12/17[/TD]
[/TR]
[TR]
[TD="align: center"]11[/TD]
[TD="align: center"]NUMBER OF SAMPLE COMPLETED[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]=[/TD]
[TD="align: center"]TO DATE[/TD]
[TD="align: center"]25/12/17[/TD]
[/TR]
[TR]
[TD="align: center"]12[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[/TR]
</tbody>[/TABLE]
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.