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]

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

<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>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=c0c0c0]#c0c0c0[/URL] , align: center"]No. of sample[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=c0c0c0]#c0c0c0[/URL] , align: center"]Status[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=c0c0c0]#c0c0c0[/URL] , align: center"]Date[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=c0c0c0]#c0c0c0[/URL] "]
[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]2[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]C
[/TD]
[TD="align: center"]05/11/2017
[/TD]
[TD="align: center"]Stability[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]3[/TD]
[TD="align: center"]11[/TD]
[TD="align: center"]C
[/TD]
[TD="align: center"]31/11/2017
[/TD]
[TD="align: center"]Stability
[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]4[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]09/12/2017
[/TD]
[TD="align: center"]Stability[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]5[/TD]
[TD="align: center"]8[/TD]
[TD="align: center"]P
[/TD]
[TD="align: center"]25/12/2017
[/TD]
[TD="align: center"]Routine
[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]6[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]C
[/TD]
[TD="align: center"]12/12/2017
[/TD]
[TD="align: center"]Stability[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]7[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]18/11/2017
[/TD]
[TD="align: center"]Stability[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]8[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]9[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , 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"]05/11/017
[/TD]
[TD="align: center"]Total[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , 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"]20/11/2017
[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]12[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]13[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]14[/TD]
[TD="align: right"]Pending[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: right"]0
[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]15[/TD]
[TD="align: right"]Completede[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: right"]23
[/TD]

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