x
• Status: Solved
• Priority: Medium
• Security: Public
• Views: 354

# Countif

Hi,

I have attached a ss where im trying to build some analysis.

In tab "Analysis" i would like to count the number of instances where ratings have changes

STD to HIGH
HIGH to STD
etc

In tab input D1 there is previous rating and current rating, in tab "Loookup Tables" i have rated High=1, MED=2 & STD =3.

So if 15 records moved from STD to HIGH and 23 from HIGH to STD, the countifs would return

15
23

Can someone suggest the countif?

Many thanks
Seamus
EE-Analysis-Template.xlsx
0
Seamus2626
1 Solution

Commented:
Try:

=SUMPRODUCT(('Input D1'!\$DQ\$2:\$DQ\$1306="Standard")*('Input D1'!\$DW\$2:\$DW\$1306="Medium"))

and change the criteria as required.

If you were to input the first criteria down the left column, e.g. Standard, Medium, High, etc

and then the same criteria across the top row, then us can use the formula

=SUMPRODUCT(('Input D1'!\$DQ\$2:\$DQ\$1306=\$A9)*('Input D1'!\$DW\$2:\$DW\$1306=B\$8))

where A9 and B8 are the first column/row headers to compare, then you can copy formula across and down, instead of manually changing the criteria in each formula.
0

Author Commented:
Perfect!

Thanks NB_VC
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.