CountIfs Macro Issue with time and count

[carden2]

Please Login or Register  to view this content.

My first cell is correct but the others some are right and some are wrong. I am trying to see which ones are alike and get the count. I don't need to use column E. I am comparing columns A, B, C, D and then counting the value in the State Column. I want to do this with a macro. Currently my macro returns these results for Producing in Column G. The time from AM to PM seems to be affecting it. Any ideas how to fix this?

[carden2]

will times not compare without "=" or something. im trying to read other threads but still not getting the correct result

[carden2]

could anyone give me some advice on this? thanks.

[TMS]

Look again at the comparison for column D.


Regards, TMS

[carden2]

that seems to work! Thanks. Anyway to get it to update each column for each row so the counts are correct all the way down. See on my highlighted screenshot I was hoping to have it still show how many are producing down and frozen all the way throughout so when one goes frozen it should have producing 4, frozen 1, down 0 then below when 2 go down it should be like producing 4 down 2 frozen 0.

[TMS]

You're welcome. Thanks for the rep.

I'm not really sure what you are asking for. Your formula is using columns B, C, D and F as criteria. It looks as though it's doing what you asked it to.


Regards, TMS

[carden2]

No problem. I appreciate the help. I am looking to remove all the duplicates because I am just doing a few steps at a time and trying to build up to what I need. I am good with vba but mainly in Access more then Excel.

I am wanting this to run as a macro and I want it to have the correct counts for every row. My screenshot if you look at the highlighted rows this what I am trying to fix.

The first highlight shows 1 producing but it actually should be 5 producing, 0 down, 0 frozen

The second highlight should show 5 producing and 1 frozen

The third highlight has the two alike rows they both should show 6 producing 0 down 0 frozen

Then under that where two rows are down. It should say 4 producing 2 down 0 frozen

Then at 12:51 AM both should go back to 6 producing 0 down 0 frozen

See I want to get these counts like that so I can remove the duplicates then I have to split some production records into these time slots with the counts. The counts are very important for me because I am using them for efficiency formulas.

[TMS]

The first highlight shows 1 producing but it actually should be 5 producing, 0 down, 0 frozen

Well, it's not going to do that because you've included the shift end time in the criteria and that shift end is different than all the others.

The second highlight should show 5 producing and 1 frozen

Again, it's not going to do that because you have a different shift start time.

Like I said, you are getting what you asked for.


Regards, TMS

[carden2]

How could I get the results I am looking for? IS there a different formula or something.

[TMS]

I don't know. Please upload a sample workbook with two sheets: 1) what you currently get from your formula and 2) mocked up - what you want to get.

Please don't upload any more pictures. All I can do is look at them. I have no plans to reproduce your data, guess what you hope to see and then work out a formula to do it.

Regards, TMS

[carden2]

Alright I attached the example. I hope this is easier to understand. Sorry about all the pictures.

[carden2]

Does this help at all?

[TMS]

Not sure if this helps. I can't get the answers you got.

Regards, TMS

[carden2]

its not bad, thanks for the help, I'm basically just looking for a summary on sheet 2.

sheet 1 is machine states throughout the day so i was hoping to remove duplicates but make counts for each state during the time period. There shouldnt be more then 6 machines producing at a time so when 6 are producing and its state changes to frozen or down it would just change for instance 6 producing 0 down 0 frozen to 5 producing 0 down 1 frozen.