How to categorize data in weeks?

Hi all,

Hope all are well.

In attached file, sheet Pivot-Closed contains a table at Row 47 as Weekly nRFT.

Column T contains same week several times. Can it be just once like for January Week 1, Week 2 .... Week 6.

The formulae in J52 and K52 are to be applied for columns U and V using same conditions but as per Weeks under column T.

Also, column R is just for reference. Can column S and T be populated without the need of column R so that it can be removed?

Can someone please help

1. Column T contains same week several times. Can it be just once like for January Week 1, Week 2 .... Week 6.
2. The formulae in J52 and K52 are to be applied for columns U and V using same conditions but as per Weeks under column T.
3. Also, column R is just for reference. Can column S and T be populated without the need of column R so that it can be removed?

Please see the attached.
RE: Question 1 --- I was unable to use Table syntax for this thread. Table syntax for formulas like these get awkward in the extreme. See this formula in T52

Formula:

=IFERROR(IF(WEEKNUM(R52,2)=WEEKNUM(R51,2),"","Week "&WEEKNUM(R52,2)),"Week "&WEEKNUM(R52,2))

Question 2 --- These two formulas in J52 and K52 respectively

Formula:

=IF(Table1[@Week2]="","",SUMPRODUCT( (Closed!$G$4:$G$5045={"TSCO REQ"}) * (TEXT(Closed!$N$4:$N$5045,"mmmmyyyy") =$I52&2023)))

Formula:

=IF(Table1[@Week2]="","",SUMPRODUCT( (Closed!$G$4:$G$5045={"TSCO REQ"}) * (LEFT(Closed!$I$4:$I$5045,6)="Repeat") * (TEXT(Closed!$N$4:$N$5045,"mmmmyyyy") =$I52&2023)))

Question 3 --- My first thought is "Yes, probably." My next thought is "I wouldn't advise it. Table syntax renders required formula workarounds extremely awkward."

I am confused. Until now there has been no mention of columns U and V and what it is they are to count or how they relate to the rest of your questions.

Are the rest of my responses helpful?

Ah yes. I see them now. I was looking for bold text as in the other columns. I will look again and get back.

I want the same count with same conditions as in columns J and K in columns U and V but on weekly basis i.e. total count in Jan Week1, Week2 and so on.

Verbal descriptions leave too much to interpretation/imagination. Please upload a new workbook with examples of desired results hand typed to show what you want.

Can the blanks between Week2 and Week 3 of January be eliminated such that the rows are continuous Jan - Week 1, Week 2?

I thought that is what you already had in the original upload. Again please include hand typed examples of what you want in a new upload.

Hi

Let me clarify.

Column U to show count of tasks closed in a particular week with below conditions:

where,

Column G in sheet "Closed" = TSCO REQ

Column N = Date to be considered

For example,

1st week of Jan had date 01.01.2023 and no task were closed on that day. Hence cell U52 = 0

2nd week of Jan had dates 2 to 08.01.2023 in which 28 tasks were closed. hence cell U53 = 28

The data shall so on be populated on weekly basis.


The same weekly data for column V for below conditions in sheet "Closed"

where,

Column G = TSCO REQ or TSCO SKP

Column I = Repeat Data Prov. Task: See Long Text

Column N = Date to be considered

For example,

Tasks with above conditions exist on 02, 21 and 28 Feb. Hence, cells V58 = 1, V61 = 1, V62 = 1


Coming to Column T, Jan had 6 weeks. So for January in Column S, column T should have 6 rows as Week 1 up to Week 6 without any additional rows in between.

Hope i was able to explain

rizwanulhasan I can't follow the explanation(s). Let me see if I can get some community help on this one.
Sorry.

Originally Posted by rizwanulhasan

However, since the formulae have been identified, this post should now be moved to charts section, but i am unsure how to do that

I have not read through this post, but based on your last response, it seems at least one part of your query has been answered. We do not simply move post for section to section just because. If this has been answered, please mark as solved and then open a new post. Plus, not sure too many people are going to read thru this entire thread to see what should be done next.