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]

Yes it working, but it consider day only in date format, It should consider day, month, year. Is there any solution.
Thanks

Care to be specific? Post #9 for example include date criteria in full, i.e. day, month , and year?

 

[babaso_tawase]

For example, data having from date 05/02/18 to 20/03/18 and I want Conditional sum from date 05/03/18 to 10/03/18 then formula takes sum from 05/02/18 to 10/03/18 , considering only day and not month and year.

 

[Aladin Akyurek]

For example, data having from date 05/02/18 to 20/03/18 and I want Conditional sum from date 05/03/18 to 10/03/18 then formula takes sum from 05/02/18 to 10/03/18 , considering only day and not month and year.

Care to specify the conditions for the data that you see in post #9 along with the outcome for that data?

 

[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

<colgroup><col style="width:30px; "><col style="width:226px;"><col style="width:68px;"><col style="width:75px;"><col style="width:96px;"><col style="width:75px;"><col style="width:87px;"></colgroup><tbody>
</tbody>

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)) <tbody> </tbody>

<tbody>
</tbody>

In this example sum of total completed sample from 05/11/2017 to 20/11/2017 should be 15 but formula result gives 23. Means it takes sum of date 05 to 20 of both month 11 and 12 instead of only 11 month.
Thanks

 

[Aladin Akyurek]

What does the following test returns in your workbook?

=ISNUMBER(C3)

 

[babaso_tawase]

It gives result = FALSE in my workbook.
For every date more than 12 gives FALSE result.

 

[Aladin Akyurek]

It gives result = FALSE in my workbook.
For every date more than 12 gives FALSE result.

That means the dates you have are not true dates.

What does this give?

=ISNUMBER(C3+0)

 

[babaso_tawase]

Formula =ISNUMBER(C3+0) also gives result FALSE.

 

[Aladin Akyurek]

Formula =ISNUMBER(C3+0) also gives result FALSE.

Care to post what you have in C3 and state yourself what that is?

 

[babaso_tawase]

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