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



## babaso_tawase (Dec 27, 2017)

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 


ABC1No. of sampleStatusDate210P20/12/17311P21/12/1745C20/12/1758C22/12/1763P23/12/1775C27/12/17

<tbody>

</tbody>


9ABCDE10NUMBER OF SAMPLE PENDINGP=FROM DATE20/12/1711NUMBER OF SAMPLE COMPLETEDC=TO DATE25/12/1712

<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 (Dec 28, 2017)

Try:Excel WorkbookABCDE1No. of sampleStatusDate210P12/20/2017311P12/21/201745C12/20/201758C12/22/201763P12/23/201775C12/27/20178910NUMBER OF SAMPLE PENDINGP=FROM DATE12/20/201711NUMBER OF SAMPLE COMPLETEDC=TO DATE12/25/20171213Pending314Completed2Sheet


----------



## babaso_tawase (Dec 28, 2017)

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


----------



## AhoyNC (Dec 28, 2017)

Then this:Excel WorkbookABCDE1No. of sampleStatusDate210P12/20/2017311P12/21/201745C12/20/201758C12/22/201763P12/23/201775C12/27/20178910NUMBER OF SAMPLE PENDINGP=FROM DATE12/20/201711NUMBER OF SAMPLE COMPLETEDC=TO DATE12/25/2017121314Pending2415Completede13Sheet


----------



## babaso_tawase (Dec 28, 2017)

Yes ,It's working.
Thanks for reply.


----------



## babaso_tawase (Jan 3, 2018)

Hi, data as

A


B


C
1No. of sampleStatusDate                      Type
210P20/12/17                 Routine
311P21/12/17                 Routine
45C20/12/17                 Routine
58C22/12/17                 Stability
63P23/12/17                 Stability
75C27/12/17                 Routine


<tbody>

 </tbody> 


9ABCDE                 F
10NUMBER OF SAMPLE PENDINGP=FROM DATE20/12/17    *Routine*
11NUMBER OF SAMPLE COMPLETEDC=TO DATE25/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 (Jan 4, 2018)

*






 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,


ABCD1No. of sampleStatusDateType210P20/12/17Stability311P21/12/17Routine45C20/12/17Stability58C22/12/17Routine63P23/12/17Stability75C27/12/17Stability

<tbody>

</tbody>



9ABCDEF10NUMBER OF SAMPLE PENDINGP=FROM DATE20/12/17*Routine*11NUMBER OF SAMPLE COMPLETEDC=TO DATE25/12/1712

<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 (Jan 5, 2018)

Try:Excel WorkbookABCDEF1No. of sampleStatusDate210P12/20/2017Stability311P12/21/2017Routine45C12/20/2017Stability58C12/22/2017Routine63P12/23/2017Stability75C12/27/2017Stability8910NUMBER OF SAMPLE PENDINGP=FROM DATE12/20/2017Total11NUMBER OF SAMPLE COMPLETEDC=TO DATE12/25/2017121314Pending2415Completede13Sheet


----------



## Aladin Akyurek (Jan 5, 2018)

A somewhat shorter set up...


Row\ColA​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 (Mar 7, 2018)

AhoyNC said:


> Try:
> 
> 
> ABCDEF1No. of sampleStatusDate   210P12/20/2017Stability  311P12/21/2017Routine  45C12/20/2017Stability  58C12/22/2017Routine  63P12/23/2017Stability  75C12/27/2017Stability  8      9      10NUMBER OF SAMPLE PENDINGP=FROM DATE12/20/2017Total11NUMBER OF SAMPLE COMPLETEDC=TO DATE12/25/2017 12      13      14Pending24    15Completede13
> ...



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


----------



## babaso_tawase (Dec 27, 2017)

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 


ABC1No. of sampleStatusDate210P20/12/17311P21/12/1745C20/12/1758C22/12/1763P23/12/1775C27/12/17

<tbody>

</tbody>


9ABCDE10NUMBER OF SAMPLE PENDINGP=FROM DATE20/12/1711NUMBER OF SAMPLE COMPLETEDC=TO DATE25/12/1712

<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 (Mar 7, 2018)

babaso_tawase said:


> 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 (Mar 7, 2018)

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 (Mar 7, 2018)

babaso_tawase said:


> 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 (Mar 7, 2018)

ABCDEF1No. of sampleStatusDate 
 
 
210C
05/11/2017
Stability 
 
311C
31/11/2017
Stability
 
 
45C09/12/2017
Stability 
 
58P
25/12/2017
Routine
 
 
63C
12/12/2017
Stability 
 
75C18/11/2017
Stability 
 
8 
 
 
 
 
 
9 
 
 
 
 
 
10NUMBER OF SAMPLE PENDINGP=FROM DATE05/11/017
Total11NUMBER OF SAMPLE COMPLETEDC=TO DATE20/11/2017
 
12 
 
 
 
 
 
13 
 
 
 
 
 
14Pending0
 
 
 
 
15Completede23
 
 
 
 


 <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*
CellFormulaB14=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 (Mar 7, 2018)

What does the following test returns in your workbook?

=ISNUMBER(C3)


----------



## babaso_tawase (Mar 11, 2018)

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


----------



## Aladin Akyurek (Mar 11, 2018)

babaso_tawase said:


> 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 (Mar 13, 2018)

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


----------



## Aladin Akyurek (Mar 13, 2018)

babaso_tawase said:


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



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


----------



## babaso_tawase (Mar 15, 2018)

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


----------



## babaso_tawase (Dec 27, 2017)

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 


ABC1No. of sampleStatusDate210P20/12/17311P21/12/1745C20/12/1758C22/12/1763P23/12/1775C27/12/17

<tbody>

</tbody>


9ABCDE10NUMBER OF SAMPLE PENDINGP=FROM DATE20/12/1711NUMBER OF SAMPLE COMPLETEDC=TO DATE25/12/1712

<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 (Mar 15, 2018)

babaso_tawase said:


> 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 (Jan 19, 2022)

babaso_tawase said:


> ABCDEF1No. of sampleStatusDate210C05/11/2017Stability311C31/11/2017Stability45C09/12/2017Stability58P25/12/2017Routine63C12/12/2017Stability75C18/11/2017Stability8910NUMBER OF SAMPLE PENDINGP=FROM DATE05/11/017Total11NUMBER OF SAMPLE COMPLETEDC=TO DATE20/11/2017121314Pending015Completede23
> 
> 
> 
> ...


----------



## babaso_tawase (Dec 28, 2022)

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



ABCDEF1No. of sampleStatusDate210C05-11-17Stability311C31/11/2017Stability45C09-12-17Stability58P25-12-17Routine63C12-12-17Stability75C18-11-17Stability8910NUMBER OF SAMPLE PENDINGP=FROM DATE05/11/017Total11NUMBER OF SAMPLE COMPLETEDC=TO DATE20-11-17121314Pending015Completede23




*Spreadsheet Formulas*

CellFormulaB14=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.*


----------

