CODEWITHSUNDEEP
excel
fcorte
fcorte

Reputation: 3

Excel Help - COUNTIFS

I need help to count a range of values in Excel.

Basically, I want to count all the cells in column B that contains a certain value ("Yes"), but only if the week number date in column A is equal to, say, 15. In column A I only have dates, so I need them to convert them to week numbers first by using WEEKNUM.

What I have tried is COUNTIFS (B:B, "= Yes", WEEKNUM(A:A), =15), but this throws an error.

I have also tried the variant COUNTIFS(B:B, "= Yes", A:A, WEEKNUM(A:A)=15). This does not throw an error but it returns 0 (when I know that there are "Yes" values in B:B).

Are you guys able to help? If possible, I would implement this with a formula without using VBA.

Thank you in advance.

Regards,

Upvotes: 0

Views: 196

Answers (1)

Gary's Student
Gary's Student

Reputation: 96791

Consider using a restricted form of :

=SUMPRODUCT(--(B:B="Yes")*(WEEKNUM(A:A+0)=15))

enter image description here

The restriction should limit the column range to increase the calculation speed.

Upvotes: 1

Related Questions