CODEWITHSUNDEEP
excelexcel-formulavba
Kittu
Kittu

Reputation: 65

how to calculate values using COUNTIF

I have two sheets in my Excel file: Sheet1 and Sheet2.

In Sheet2 i have data with columns Severity and LodgedDate. In Severity there are multiple values like 

sev-1
sev-2
sev-3
sev-4

In Sheet1 I want to calculate the no. of occurrences for sev-1, sev-2, sev-3, sev-4 for today, this month, this financial quarter, last quarter, older than last quarter.

Can anyone help?

Like

=COUNTIF(shee2, C2:C,"sev-1" , "Today").

Upvotes: 0

Views: 76

Answers (1)

teylyn
teylyn

Reputation: 35915

Assuming the date is in column B, try along the lines of

=countifs(Sheet2!C:C,"sev-1",Sheet2!B:B,today())

For the current month

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

So, for a time frame enter the first and the last day of the time frame into two conditions.

You may want to use a pivot table instead of countifs formulas. It is a lot faster and won't require you to type out all the possibilities.

Upvotes: 2

Related Questions