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

 

[AhoyNC]

Try:

Excel Workbook
	A	B	C	D	E
1	No. of sample	Status	Date
2	10	P	12/20/2017
3	11	P	12/21/2017
4	5	C	12/20/2017
5	8	C	12/22/2017
6	3	P	12/23/2017
7	5	C	12/27/2017
8
9
10	NUMBER OF SAMPLE PENDING	P	=	FROM DATE	12/20/2017
11	NUMBER OF SAMPLE COMPLETED	C	=	TO DATE	12/25/2017
12
13	Pending	3
14	Completed	2
Sheet

 

[babaso_tawase]

Thanks for reply,
Actually I want sum of numbers corresponding to status pending or completed.

 

[AhoyNC]

Then this:

Excel Workbook
	A	B	C	D	E
1	No. of sample	Status	Date
2	10	P	12/20/2017
3	11	P	12/21/2017
4	5	C	12/20/2017
5	8	C	12/22/2017
6	3	P	12/23/2017
7	5	C	12/27/2017
8
9
10	NUMBER OF SAMPLE PENDING	P	=	FROM DATE	12/20/2017
11	NUMBER OF SAMPLE COMPLETED	C	=	TO DATE	12/25/2017
12
13
14	Pending	24
15	Completede	13
Sheet

 

[babaso_tawase]

Yes ,It's working.
Thanks for reply.

 

[babaso_tawase]

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

<tbody>
</tbody>

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

<tbody>
</tbody>

In F10 cell I created Data validation list, for selection Routine or stability or Total.and formula used as

=SUMPRODUCT(--($B$2:$B$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),

While selecting stability or Routine it gives right result but for total of stability and Routine does not work formula.

I want formula such it gives sum of Routine and stability samples as well as for either stability or Routine.

Thanks.

 

[babaso_tawase]

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

I want help for this,you already replied this thread. I want add one more criteria as below,

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

<tbody>
</tbody>

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

<tbody>
</tbody>

In F10 cell I created Data validation list, for selection Routine or stability or Total.and formula used as

=SUMPRODUCT(--($B$2:$B$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),

While selecting stability or Routine it gives right result but for total of stability and Routine does not work formula.

I want formula such it gives sum of Routine and stability samples as well as for either stability or Routine.

Thanks.

 

[AhoyNC]

Try:

Excel Workbook
	A	B	C	D	E	F
1	No. of sample	Status	Date
2	10	P	12/20/2017	Stability
3	11	P	12/21/2017	Routine
4	5	C	12/20/2017	Stability
5	8	C	12/22/2017	Routine
6	3	P	12/23/2017	Stability
7	5	C	12/27/2017	Stability
8
9
10	NUMBER OF SAMPLE PENDING	P	=	FROM DATE	12/20/2017	Total
11	NUMBER OF SAMPLE COMPLETED	C	=	TO DATE	12/25/2017
12
13
14	Pending	24
15	Completede	13
Sheet

 

[Aladin Akyurek]

A somewhat shorter set up...

Row\Col	A​	B​	C​	D​	E​	F​
1​	No. of sample​	Status​	Date​	Type​
2​	10​	P​	20/12/17​	Stability​
3​	11​	P​	21/12/17​	Routine​
4​	5​	C​	20/12/17​	Stability​
5​	8​	C​	22/12/17​	Routine​
6​	3​	P​	23/12/17​	Stability​
7​	5​	C​	27/12/17​	Stability​
8​
9​	​	​	​	​	​	​
10​						Total​
11​	NUMBER OF SAMPLE PENDING​	P​	=​	FROM DATE​	20/12/17​	24​
12​	NUMBER OF SAMPLE COMPLETED​	C​	=​	TO DATE​	25/12/17​	13​

In F11 enter and copy down:

=SUMPRODUCT(SUMIFS($A$2:$A$7,$B$2:$B$7,$B11,$C$2:$C$7,">="&$E$11,$C$2:$C$7,"<="&$E$12,$D$2:$D$7,IF($F$10="total","?*",$F$10)))

 

[babaso_tawase]

Try:

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

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

Excel tables to the web >> Excel Jeanie HTML 4

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