Detect if there is a public holiday an closes collectively holiday in row

Hi

The example is a calendar. In j5 i want to make the some of the public holiday + collectively holliday. So there is no collectively holdiay only a public holiday so in j5 =1

The collerd cells are the public holiday and collectively holliday made by conditional formating.

How can i do that.

I think that you're going to struggle for a good answer without translating the contents of your sample file.

This looks right, but I don't know the function translations.

=SUMPRODUCT(COUNTIFS(Feestdagen,B5:H5,Feestdagen,">0")+COUNTIFS('Feestdagen Verlofcodes'!$D$4:$D$16,B5:H5,'Feestdagen Verlofcodes'!$D$4:$D$16,">0"))

Thnx but ons tuin u in de formula i dont understand !
What range is declared to feestdagen
I cant remember that I make a name feestdagen
I now how to translate the formula
Look tomorrow of later on.
I let you now it works

@jason.b75
Did you test the formula it's not working

Define 'not working'

It gave me results, whether or not those results match what you expect is another matter. As I don't understand the Belgian language, there was guesswork involved.

test it in de example

What i want is that there where x is count how many public hoilidays there are in te week

i test it with aantallen.als = countifs
countif(feestdagen;b5:h5;feestdagen;">0") give nothing

now its working !

But why must there sumproduct in the formula ?
I look at this to understand countifs or aantallen.als
https://support.office.com/en-us/art...c-aa8c2a866842
Why they dont need sumproduct

The examples that you looked at on the support page are basic, the method used here is slighly advanced.

As I said in my previous post, when a criteria refers to more than 1 value or cell, an array function is needed to complete the calculation.

B5:H5 is a range of 7 cells, which is more than 1, so needs sumproduct to complete the calculation.

The array can be completed with other functions as well, but for what you need sumproduct is the correct one to use.

Rule 08: Cross-posting Without Telling Us

Your post does not comply with Rule 8 of our Forum RULES. Do not cross-post your question on multiple forums without telling us about your threads on other forums.

Post a link to any other forums where you have asked the same question. If you have fewer than 10 posts here, you will not be able to post a link, but you must still tell us where else you have asked the question.

Cross-posting is when you post the same question in other forums on the web. The last thing you want to do is waste people's time working on an issue you have already resolved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post.

Expect cross-posted questions without a link to be closed and a message will be posted by the moderator explaining why. We are here to help so help us to help you!

Read this to understand why we ask you to do this, and then please edit your first post to include links to any and all cross-posts in any other forums (not just this site).

If you have less than 10 posts, do not try to copy and paste the link. Instead, type the link out in your thread.

No further help to be offered, please, until the OP has complied with this request.

it must be closed