• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

I need help with formula to find out totals based on month.

I would like to know if there is a formula to find out the total of reports based on a specific month? For example, how many guest reports were made in all January month 2021?
Can you help me with that please? I am sending you the original file attached.

Thank you in advance.
 

Attachments

  • Test.xlsx
    25 KB · Views: 10
Is this your searching =COUNTIFS(Sheet1!$B:$B,$A3,Sheet1!$A:$A,">="&"1/1/2021",Sheet1!$A:$A,"<="&"1/31/2021"),
refer attached file.
 

Attachments

  • Test (1).xlsx
    27.7 KB · Views: 5
The formula to study is COUNTIFS. The data was somewhat short of structure (Tables or Names or even headings/annotation) but here is a possibility:
Code:
= COUNTIFS(
  Table1[ReportedBy],   "Guest",
  Table1[DateReported], ">=1/2/21",
  Table1[DateReported], "<1/3/21" )

73265
 
Formula in cell G2 of the attached.
More flexible: Pivot tables, but needs headings in the source data which I've added.
 

Attachments

  • Chandoo45766Test.xlsx
    41 KB · Views: 7
Or else you can use the below formula
in Cell F2 =COUNTIFS($B$5:$B$298,"Guest",$A$5:$A$298,">="&"1/1/2021",$A$5:$A$298,"<="&"1/31/2021")
in Cell F3 =COUNTIFS($B$5:$B$298,"Guest",$A$5:$A$298,">="&"2/1/2021",$A$5:$A$298,"<="&"2/28/2021")
 

Attachments

  • Test (1) (1).xlsx
    27.6 KB · Views: 7
F2: =SUMPRODUCT(($B$5:$B$298="Guest")*($A$5:$A$298>=DATE(2021,1,1))*($A$5:$A$298<=DATE(2021,1,31)))
F2: =COUNT(IF($B$5:$B$298="Guest",IF($A$5:$A$298>=DATE(2021,1,1), IF($A$5:$A$298<=DATE(2021,1,31),)))) Ctrl+Shift+Enter
 
And why not a simple Pivot Table after adding some headers and making your range an Excel Table to make it dynamic?
Use slicers if needed?
 

Attachments

  • Copy of Test.xlsx
    41.6 KB · Views: 8
Back
Top