0

Please refer to this image Look at C and D column.

enter image description here How many unique "week number" a "day" have. For example how many unique week number sunday have, monday and so on,

Basically i want to know how many different monday(different date), how many different friday, and so on, occur in the C column.

1

You can use the "1/COUNTIFS" scheme which is "magic" for problems involving uniqueness.

=SUMPRODUCT(($C$2:$C$15="Sunday")/COUNTIFS($C$2:$C$15,$C$2:$C$15,$D$2:$D$15,$D$2:$D$15))

But be careful that this formula needs that your ranges have no blank gaps. also always avoid full (or too large) columns for formulas involving array computations, because they're slow.

p.s. you can replace the literal "Sunday" with any cell reference.

  • Hi, Thanks for your answer, But i need this for all days in the week, Not only for sunday – beginner Jul 7 '17 at 19:21
  • @beginner IIUC, you need to count the total number of unique combinations (day, weekNumber)? Otherwise you can put the week days below each other somewhere and reference their cells in the formula. – A.S.H Jul 7 '17 at 19:27
  • Sorry I am totally new to Excel. i don't get you . I want something like these Sunday=>week_number23 ,week_number24.... So that i can count these week number to find out how many times Sunday (different days) occur. Can't we use pivot for this. Is there any other easy way to do so, – beginner Jul 7 '17 at 19:31
  • @beginner I understood that you wanted to count them, not to list them (see "how many" in your question). May be you can have the list with pivot tables idk. – A.S.H Jul 7 '17 at 19:45
  • 1
    I think that he thinks that it has to be done in two steps. @beginner, you don't have to do it in two steps. The formula that ASH gave you will calculate how many distinct Sundays are in the array. Copy and paste the formula into a cell to test it. For other days of the week, just change "Sunday" to "Monday" or any other day. – Bandersnatch Jul 7 '17 at 19:45

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.