Excel Help - COUNTIFS

0
votes

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,

1
excel
You will need to make a "helper column" that calculates the weeknumber, then just reference the helper column range in your countifs formula – Nathan Clement
Weeknum is one of those formulas that do not work with an array as the criterion. So the easy answer it what @NathanClement stated. – Scott Craner

1 Answers

1
votes

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.