Calculate values using COUNTIF

[narsing18]

Hi,

I have 2 sheets

sheet1 is a Summary sheet and sheet2 is data sheet

sheet 2 has columns from A TO R, in that column G is Severity (heading) with four values Sev-1, Sev-2, Sev-3,Sev-4 and J is date Field with heading Lodgeddate.

i want to calculate severity count for today, this month, this financial quarter, last quarter, older than last quarter.
i have used below formula to check sev-1 count in sheet2 but its not showing any value but just showing #VALUE! Symbol

=COUNTIFS(Sheet2!G:G,"sev-1",Sheet2!J:J,">=1-Mar-2017",Sheet2!J:J,"<31-Mar-2017") 

please help to solve this problem.

 

[Kamolga]

Re: how to calculate values using COUNTIF

Try:

[COLOR=#141414][FONT=Consolas]=COUNTIFS(Sheet2!G:G,[/FONT][/COLOR][COLOR=#800000][FONT=Consolas]"sev-1"[/FONT][/COLOR][COLOR=#141414][FONT=Consolas],Sheet2!J:J,[/FONT][/COLOR][COLOR=#800000][FONT=Consolas]">="&1-Mar-2017[/FONT][/COLOR][COLOR=#141414][FONT=Consolas],Sheet2!J:J,[/FONT][/COLOR][COLOR=#800000][FONT=Consolas]"<"&31-Mar-2017[/FONT][/COLOR][COLOR=#141414][FONT=Consolas]) [/FONT][/COLOR]

 

[pgc01]

Re: how to calculate values using COUNTIF

Hi

Try:

=COUNTIFS(Sheet2!G:G,"sev-1",Sheet2!J:J,">="&DATE(2017,3,1),Sheet2!J:J,"<"&DATE(2017,3,1))

 

[Kamolga]

Re: how to calculate values using COUNTIF

Hi

Try:

=COUNTIFS(Sheet2!G:G,"sev-1",Sheet2!J:J,">="&DATE(2017,3,1),Sheet2!J:J,"<"&DATE(2017,3,1))

I think you meant

[FONT=Consolas]=COUNTIFS(Sheet2!G:G,"sev-1",Sheet2!J:J,">="&DATE(2017,3,1),Sheet2!J:J,"<"&DATE(2017,3,[U][COLOR=#ff0000]31[/COLOR][/U]))[/FONT]

 

[pgc01]

Re: how to calculate values using COUNTIF

Thank you Kamolga, for pointing out the typo.