Countifs with cells that contain multiple data

1. Countifs with cells that contain multiple data

Hi

Hope someone can help as this is sadly beyond my Excel know how.

I have a sheet that I need to count occurrences from. I can handle cells with single data but I need to have 1 cell that has days of the week in. In the attached sheet cell C5 has a data validation to select days of the week which is controlled by a little VBA code. Can someone assist with the formula required to search the sheet using criteria in C5 to C9

Any help is greatly appreciated.

Thanks

2. Re: Countifs with cells that contain multiple data

Try

=SUM(COUNTIFS(Data!\$D:\$D,">="&C\$8,Data!\$E:\$E,"<="&C\$9,Data!F:F,"="&\$C\$7,Data!G:G,"="&\$C\$6,Data!H:H,{"Wed","Thu"}))

3. Re: Countifs with cells that contain multiple data

Hi,

=SUMPRODUCT(COUNTIFS(Data!D:D,">="&C8,Data!E:E,"<="&C9,Data!F:F,C7,Data!G:G,C6,Data!H:H,FILTERXML("<a><b>"&SUBSTITUTE(C5,",","</b><b>")&"</b></a>","//b")))

Regards

4. Re: Countifs with cells that contain multiple data

If days are in E1:E2

E1=LEFT(C5,3)
E2=Right)C5,3)

=SUMPRODUCT(COUNTIFS(Data!\$D:\$D,">="&C\$8,Data!\$E:\$E,"<="&C\$9,Data!F:F,"="&\$C\$7,Data!G:G,"="&\$C\$6,Data!H:H,E1:E2))

but you have a 365 solution anyway

5. Re: Countifs with cells that contain multiple data

Amazing, thanks. Would really like to understand how that worked.

6. Re: Countifs with cells that contain multiple data

I assume your question is for XORLX.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1