Countif with data validation

[yukibare]

Hello all, Happy new year.

This one has proved to be quite the challenge for me:

Opening the workbook attached, you will see 3 sheets, Grid model, Dates for month and Interruptions.


The grid model contains all the values to be counted on a daily basis, the Dates for month is the data validation sheet for ('Interruptions' cell F1) which allows results on the interruption sheet change when a different date is picked form the drop down list.

I want to replicate cell F1 in Cell O1 with a different count criteria such that whenever an instance occurs (EF, OC, IMB, EMG, etc...) it's counted as 1 value. The values in Cells O4 - V4 and below are all correct for the 1ST of December, however these are manually counted results and I would like a formula that could do this instantly and not just for 1 day but for the entire month.

[Glenn Kennedy]

In O4, copied across and down:

=COUNTIFS(INDEX('Grid model'!$C$2:$BY$769,,MATCH(Interruptions!$B4,'Grid model'!$C$1:$BY$1,0)),O$3,'Grid model'!$A$2:$A$769,Interruptions!$O$1)

I only pasted the formula into the dark green cells. You can do the remainder, taking into account your totals rows. No adjustment of the formula is needed. It is just fiddly... so you can do it!!

[yukibare]

Alrght Glen! So this is very good, thank you. Unfortunately it does not work for all the cells, see R10 for example. It should work out to 24 hours but it doesn't count?

[yukibare]

Also, it doesn't change with the dates as with the cell results in F - N

[Glenn Kennedy]

I can fix the date. I set it to copy O1, as you said... but if you want it to copy F1, that's easy.

Why are you expecting 24 for EMG on 1st December???

[Glenn Kennedy]

I can see none at all. The values in row 11 have been manually added. Are they correct?

[yukibare]

Row 11 has a link to another document that I broke, the values are mostly correct with some inconsistencies so please ignore them.

[Glenn Kennedy]

The date in O1 now copies the date in F1. The date in F1 controls the formula. Is there an issue with R10???

[yukibare]

No issues in R10 Glen, its working perfectly now. Might I ask why you changed the grid model to tables? Would the formula not work otherwise?

[Glenn Kennedy]

I changed nothing!!

You're welcome.

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It would also be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.

[yukibare]

Hello Glen, I've got another question please. If I wanted to do a summary of EF occurrences (Cell F4 for example) over the months duration, how is it done?

The results appear in a drop down list on the spreadsheet and have the same cell value even after I select a new date so its a bit of a struggle getting a sum total.

[AliGW]

This looks like a different query - you should start a new thread with a suitable title.

[yukibare]

Alright I will, thank you