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

[Sebastiandiaz]

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.

 

[Basavaraj K H]

Is this your searching =COUNTIFS(Sheet1!$B:$B,$A3,Sheet1!$A:$A,">="&"1/1/2021",Sheet1!$A:$A,"<="&"1/31/2021"),
refer attached file.

 

[Peter Bartholomew]

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:

= COUNTIFS(
  Table1[ReportedBy],   "Guest",
  Table1[DateReported], ">=1/2/21",
  Table1[DateReported], "<1/3/21" )

 

[p45cal]

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

 

[Basavaraj K H]

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")

 

[MercurialBoy]

A great solution!Are these all options?

 

[Hui]

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

 

[pecoflyer]

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?

 

[Sebastiandiaz]

I tested all the options and they perfectly worked. Thank you so much.

 

[Sebastiandiaz]

Is this your searching =COUNTIFS(Sheet1!$B:$B,$A3,Sheet1!$A:$A,">="&"1/1/2021",Sheet1!$A:$A,"<="&"1/31/2021"),
refer attached file.

This one worked great. Thank you

 

[Sebastiandiaz]

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:

= COUNTIFS(
  Table1[ReportedBy],   "Guest",
  Table1[DateReported], ">=1/2/21",
  Table1[DateReported], "<1/3/21" )

View attachment 73265

Thank you so much!

 

[Sebastiandiaz]

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

You did great with headings. It worked so perfectly too!