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

B

babaso_tawase

Board Regular
Joined
Feb 5, 2017
Messages
73
Office Version
  1. 2007
Platform
  1. Windows
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...
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
Sort by date Sort by votes
Try:
Excel Workbook
ABCDE
1No. of sampleStatusDate
210P12/20/2017
311P12/21/2017
45C12/20/2017
58C12/22/2017
63P12/23/2017
75C12/27/2017
8
9
10NUMBER OF SAMPLE PENDINGP=FROM DATE12/20/2017
11NUMBER OF SAMPLE COMPLETEDC=TO DATE12/25/2017
12
13Pending3
14Completed2
Sheet
 
Upvote 0
Thanks for reply,
Actually I want sum of numbers corresponding to status pending or completed.
 
Upvote 0
Then this:
Excel Workbook
ABCDE
1No. of sampleStatusDate
210P12/20/2017
311P12/21/2017
45C12/20/2017
58C12/22/2017
63P12/23/2017
75C12/27/2017
8
9
10NUMBER OF SAMPLE PENDINGP=FROM DATE12/20/2017
11NUMBER OF SAMPLE COMPLETEDC=TO DATE12/25/2017
12
13
14Pending24
15Completede13
Sheet
 
Upvote 0
[TABLE="class: cms_table, width: 500"]
<tbody>[TR]
[TD="align: center"]Hi, data as

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 Type
[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]P[/TD]
[TD="align: center"]20/12/17 Routine
[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]11[/TD]
[TD="align: center"]P[/TD]
[TD="align: center"]21/12/17 Routine
[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]20/12/17 Routine
[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]8[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]22/12/17 Stability
[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]P[/TD]
[TD="align: center"]23/12/17 Stability
[/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]27/12/17 Routine
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="class: cms_table_cms_table_cms_table, width: 500"]
<tbody>[TR]
[/TR]
[TR]
[TD][TABLE="class: cms_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 F
[/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 Routine
[/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]

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.
 
Upvote 0
icon1.png
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,

[TABLE="class: cms_table, width: 500"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/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]
[TD="align: center"]Type[/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]
[TD="align: center"]Stability[/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]
[TD="align: center"]Routine[/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]
[TD="align: center"]Stability[/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]
[TD="align: center"]Routine[/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]
[TD="align: center"]Stability[/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]
[TD="align: center"]Stability[/TD]
[/TR]
</tbody>[/TABLE]


[TABLE="class: cms_table_cms_table_cms_table, width: 500"]
<tbody>[TR]
[/TR]
[TR]
[TD][TABLE="class: cms_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]
[TD="align: center"]F[/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]
[TD="align: center"]Routine[/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]
[TD="align: center"][/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]
[TD="align: center"][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
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.
 
Upvote 0
Try:
Excel Workbook
ABCDEF
1No. 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/2017Total
11NUMBER OF SAMPLE COMPLETEDC=TO DATE12/25/2017
12
13
14Pending24
15Completede13
Sheet
 
Upvote 0
A somewhat shorter set up...

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

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)))
 
Upvote 0
AhoyNC said:
Try:

ABCDEF

<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"]P[/TD]
[TD="align: center"]12/20/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"]P[/TD]
[TD="align: center"]12/21/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"]4[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]12/20/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"]C[/TD]
[TD="align: center"]12/22/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"]P[/TD]
[TD="align: center"]12/23/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"]12/27/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"]12/20/2017[/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"]12/25/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"]24[/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"]13[/TD]

</tbody>

Spreadsheet Formulas
CellFormula
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
Click to expand...

Yes it working, but it consider day only in date format, It should consider day, month, year. Is there any solution.
Thanks
 
Upvote 0
You must log in or register to reply here.

Similar threads

SaraWitch
  • Solved
Overdue date
Replies
4
Views
890
etaf
etaf
N
  • Solved
Count days between to Table Ranges that falls in SLA intervals
Replies
4
Views
127
Nicha
N
S
  • Question Question
Excluding dates in summing up a range
Replies
3
Views
247
Fluff
Fluff
D
  • Question Question
Locate first instance of all dates in a column and hide all remaining rows
Replies
4
Views
126
Peter_SSs
Peter_SSs
S
  • Solved
Add tick mark at the end of text in a cell with conditional formatting
Replies
7
Views
452
Eric W
Eric W

Forum statistics

Threads
1,224,823
Messages
6,181,175
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top