Product tracking

[RichThompson]

I've been trying various different formulas to have excel check a for a value in a date column that is greater than or equal to a predefined start date. Then I need it to count if the dates are within condition and product operation is complete.
Eg. Start date is Monday any dates past Monday will also be checked and then a count of how many "ticks" correspond to that date. It's a task counter, how many tasks did you complete this week sort of thing.
Any help would be appreciated as I've been bashing my head against a desk for days with this.

 

[j_unsuitable]

Hey,

see if this works for you:

Excel 2007

	A	B	C	D	E
Date	Status	Start Date	Count
complete
in progress
in progress
complete
in progress
complete
in progress
in progress
complete
in progress
complete
in progress
in progress
complete
in progress
in progress
in progress
in progress
in progress
in progress

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]1-Apr-16[/TD]

[TD="align: right"][/TD]
[TD="align: right"]2-Apr-16[/TD]
[TD="align: right"]5[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]2-Apr-16[/TD]

[TD="align: right"][/TD]
[TD="align: right"]10-Apr-16[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]3-Apr-16[/TD]

[TD="align: right"][/TD]
[TD="align: right"]14-Apr-16[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]4-Apr-16[/TD]

[TD="align: right"][/TD]
[TD="align: right"]15-Apr-16[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]5-Apr-16[/TD]

[TD="align: right"][/TD]
[TD="align: right"]1-Apr-16[/TD]
[TD="align: right"]6[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]6-Apr-16[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]7-Apr-16[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]8-Apr-16[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]9-Apr-16[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]10-Apr-16[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="align: right"]11-Apr-16[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]
[TD="align: right"]12-Apr-16[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]
[TD="align: right"]13-Apr-16[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]
[TD="align: right"]14-Apr-16[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]16[/TD]
[TD="align: right"]15-Apr-16[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]17[/TD]
[TD="align: right"]16-Apr-16[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]18[/TD]
[TD="align: right"]17-Apr-16[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]19[/TD]
[TD="align: right"]18-Apr-16[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]20[/TD]
[TD="align: right"]19-Apr-16[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]21[/TD]
[TD="align: right"]20-Apr-16[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #E0E0F0"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]E2[/TH]
[TD="align: left"]=COUNTIFS($A$2:$A$21,">="&$D2,$B$2:$B$21,"complete")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Julian

 

[RichThompson]

It's Close but no banana,

no im not using my phone and am actually at a computer i can now show you what i'm trying to do.
so, firstly lets use our reference cells in the top row so we can see what's going on.

<colgroup span="12" width="85"></colgroup> <tbody>
[TD="align: left"][/TD]
[TD="align: left"]A[/TD]
[TD="align: left"]B[/TD]
[TD="align: left"]C[/TD]
[TD="align: left"]D[/TD]
[TD="align: left"]E[/TD]
[TD="align: left"]F[/TD]
[TD="align: left"]G[/TD]
[TD="align: left"]H[/TD]
[TD="align: left"]I[/TD]
[TD="align: left"]J[/TD]
[TD="align: left"]K[/TD]

[TD="align: right"]1[/TD]
[TD="align: left"][/TD]
[TD="align: left"][/TD]
[TD="align: left"][/TD]
[TD="align: left"][/TD]
[TD="align: left"][/TD]
[TD="align: left"][/TD]
[TD="align: left"][/TD]
[TD="align: left"][/TD]
[TD="align: left"][/TD]
[TD="align: right"]27/04/16[/TD]
[TD="align: left"] √ [/TD]

[TD="align: right"]2[/TD]
[TD="align: left"][/TD]
[TD="align: left"][/TD]
[TD="align: left"][/TD]
[TD="align: left"][/TD]
[TD="align: left"][/TD]
[TD="align: left"][/TD]
[TD="align: left"][/TD]
[TD="align: left"][/TD]
[TD="align: left"][/TD]
[TD="align: left"][/TD]
[TD="align: left"][/TD]

[TD="align: right"]3[/TD]
[TD="align: left"]Date[/TD]
[TD="align: left"]serial number[/TD]
[TD="align: left"]operation1[/TD]
[TD="align: left"]op2[/TD]
[TD="align: left"]op3[/TD]
[TD="align: left"]op4[/TD]
[TD="align: left"]op5[/TD]
[TD="align: left"]op6[/TD]
[TD="align: left"]op7[/TD]
[TD="align: left"][/TD]
[TD="align: left"][/TD]

[TD="align: right"]4[/TD]
[TD="align: right"]25/04/16[/TD]
[TD="align: right"]1[/TD]
[TD="align: left"] √ [/TD]
[TD="align: left"] √ [/TD]
[TD="align: left"] √ [/TD]
[TD="align: left"] √ [/TD]
[TD="align: left"] √ [/TD]
[TD="align: left"] √ [/TD]
[TD="align: left"] √ [/TD]
[TD="align: left"][/TD]
[TD="align: left"][/TD]

[TD="align: right"]5[/TD]
[TD="align: right"]26/04/16[/TD]
[TD="align: right"]2[/TD]
[TD="align: left"] √ [/TD]
[TD="align: left"] √ [/TD]
[TD="align: left"] √ [/TD]
[TD="align: left"] √ [/TD]
[TD="align: left"] √ [/TD]
[TD="align: left"] √ [/TD]
[TD="align: left"] √ [/TD]
[TD="align: left"][/TD]
[TD="align: left"][/TD]

[TD="align: right"]6[/TD]
[TD="align: right"]27/04/16[/TD]
[TD="align: right"]3[/TD]
[TD="align: left"] √ [/TD]
[TD="align: left"] √ [/TD]
[TD="align: left"] √ [/TD]
[TD="align: left"] √ [/TD]
[TD="align: left"] √ [/TD]
[TD="align: left"] √ [/TD]
[TD="align: left"] √ [/TD]
[TD="align: left"][/TD]
[TD="align: left"][/TD]

[TD="align: right"]7[/TD]
[TD="align: right"]28/04/16[/TD]
[TD="align: right"]4[/TD]
[TD="align: left"] √ [/TD]
[TD="align: left"] √ [/TD]
[TD="align: left"] √ [/TD]
[TD="align: left"] √ [/TD]
[TD="align: left"] √ [/TD]
[TD="align: left"] √ [/TD]
[TD="align: left"] √ [/TD]
[TD="align: left"][/TD]
[TD="align: left"][/TD]

[TD="align: right"]8[/TD]
[TD="align: right"]29/04/16[/TD]
[TD="align: right"]5[/TD]
[TD="align: left"] √ [/TD]
[TD="align: left"] √ [/TD]
[TD="align: left"] √ [/TD]
[TD="align: left"] √ [/TD]
[TD="align: left"] √ [/TD]
[TD="align: left"] √ [/TD]
[TD="align: left"] √ [/TD]
[TD="align: left"][/TD]
[TD="align: left"][/TD]

[TD="align: right"]9[/TD]
[TD="align: right"]30/04/16[/TD]
[TD="align: right"]6[/TD]
[TD="align: left"] √ [/TD]
[TD="align: left"] √ [/TD]
[TD="align: left"] √ [/TD]
[TD="align: left"] √ [/TD]
[TD="align: left"] √ [/TD]
[TD="align: left"] √ [/TD]
[TD="align: left"] √ [/TD]
[TD="align: left"][/TD]
[TD="align: left"][/TD]

[TD="align: right"]10[/TD]
[TD="align: right"]01/05/16[/TD]
[TD="align: right"]7[/TD]
[TD="align: left"] √ [/TD]
[TD="align: left"] √ [/TD]
[TD="align: left"] √ [/TD]
[TD="align: left"] √ [/TD]
[TD="align: left"] √ [/TD]
[TD="align: left"] √ [/TD]
[TD="align: left"] √ [/TD]
[TD="align: left"][/TD]
[TD="align: left"][/TD]

</tbody>

<colgroup span="9" width="85"></colgroup><tbody></tbody>

So i need to count number of operations complete for any days within a week. so for arguments sake say 27/04/16 was the week start and 01/05/16 was where we were today. this file will have data before the date that will be counted into a standard counter which is why, i need excel to count all the ticks in ops 1-7 only for the dates greater than or equal to the specified start date. its a weekly tracker, so the date will change every week and i only want it to count data from the current week, any older data still needs to be there within the archive file but ommited from the counter if its date is before the specified start date. if you have any ideas id greatly appreciate it. i've tried =Countifs(AA,">="&J1,C:I,K1) and well, it just isn't having any of it.
Help on this would be greatly appreciated.

 

[j_unsuitable]

Hey, maybe try this:

=SUMPRODUCT((A4:A10>=J1)*(C4:I10=K1))

An alternative with countifs would be adding up the countifs for each column, like
=countifs(A4:A10,">="&J1,C4:C10,K1)+countifs(A4:A10,">="&J1,D4:D10,K1)+countifs(A4:A10,">="&J1,E4:E10,K1)+.....

Julian

 

[RichThompson]

I think you may have just done it, ive tested it on the basic sheet i provided to the thread and the sum product function seems to work very well. if not then a large string of countifs+Countifs....... may take a while to set up but should in theory work. thanks so much.

 

[RichThompson]

Hey, maybe try this:

=SUMPRODUCT((A4:A10>=J1)*(C4:I10=K1))

An alternative with countifs would be adding up the countifs for each column, like
=countifs(A4:A10,">="&J1,C4:C10,K1)+countifs(A4:A10,">="&J1,D4:D10,K1)+countifs(A4:A10,">="&J1,E4:E10,K1)+.....

Julian

Bad news, it didn't work. Seemed to work in libre office (as I don't have excel at home) but isn't working in excel. It's just counting the number of ticks regardless of the date criteria set. Any further suggestions?

 

[j_unsuitable]

Hey,

I am using Excel 2010 and its working for me.
Maybe post the actual formula youre using, it might be theres some kink.
And finally make sure the date in J1 is right. If the date is smaller than any date in A4:A10 that would explain why it counts every tick.

 

[RichThompson]

It did work it was me being a muppet I had date columns as A:A and data range as A which made it throw up an error about unable to get all the resources to calculate. (Due to it being an infinite data source) once I'd set it to finite as in the example you gave it works fine so I've used A1:a100000 and data range as B1:B100000 now it works perfectly. Thanks again for the help.