Required Volume & Percentage table....

[@Gopigk]

Hi all,

Herewith i attached Sample excel file for your reference. I need a report for particular dates from whole data. I tried this through count-If option, but unable to get the same.

Request to see Summary sheet for required table from attached file, and do the needful

 

[NARAYANK991]

Hi ,

See the attached file.

Narayan

 

[@Gopigk]

Sir, sorry to say, I need data relates to dates between 25th to 30/31st of concerned month only. what you have provided is the same data which is in above table. Because I need to analyse how much percentage of work processing in month last 5/6 days.

 

[NARAYANK991]

Hi ,

See the revised file.

Narayan

 

[@Gopigk]

Narayan sir, thank you very much for your help. But for me it is little bit difficult to understand the functionality of sum product function. Is there any other easy formula to get this data like, countifs or anything else.?

 

[vletm]

@Gopigk
Something like this ...

 

[@Gopigk]

Thank you sir

 

[Peter Bartholomew]

There is nothing fundamentally wrong with your idea of using COUNTIFS except that the function only accepts range references for the criteria arrays. As soon as you reference the range within a function
= DAY(Posting_Date)
the result is an array but no longer a range reference. If you are prepared to insert helper columns containing this formula and @NARAYANK991's formula
=TEXT(Posting_Date,"mmm")
then the formulas
= COUNTIFS( GR_doc_no, "<>", PostingDay, ">=25", PostingMonth, Month )
= COUNTIFS( Clrng_Doc_1, "<>", PostingDay, ">=25", PostingMonth, Month )
work fine, as do the column totals
= COUNTIFS( GR_doc_no, "<>", PostingDay, ">=25" )
= COUNTIFS( Clrng_Doc_1, "<>", PostingDay, ">=25" )

For me, typing the formulas into a single cell will create the entire column of results but, unless you are using Office 365 insiders channel, you will be compelled to copy the formulas down to give monthly summaries.

While you are about it, you could also replace the pivot table with virtually identical formulas obtained by omitting the test on PostingDay, e.g.
= COUNTIFS( Clrng_Doc_1, "<>", PostingMonth, Month )

 

[bluesky63]

@Gopigk

This is the Power Query version for your reference

Data > your source
Total GR > your equivalent top pivot table
GR (25th to 30th 31st ) > the 2nd tables

both 2nd and 3rd worksheets are loaded from Power Query

click on Data > show queries, click on any queries to goto Power Query Editor, Summary 1 is Total GR, Summary 2 is GR (25th to 30th 31st ), both queries mostly using the User interface build in functions ( no need codes), except some conditional scenarios like 25th to 30th 31st

So any new rows (data) added to Data, you just need to right click > refresh to get the updated tables

PQ is easy to use especially for user who are not comfortable with advance excel commands

for your information

Christopher