Excel help - count if

[Passmethecrisps]

I have posted this in geek stuff but I was hoping someone might be able to help me here

I have a spreadsheet where I want to analyse all the offers made to students by each university.

So for example I want to count how many times Edinburgh university rejected, conditionally offered and so on.

Basically I want to count if the cell contents are Edinburgh and reject then Edinburgh and conditional and so on.

I have used the countif function like

=COUNTIF(c4:c390, “EDINB”) AND COUNTIF(F4:F390, “REJ”)

But I can’t seem to get it right. I have tried various syntax but it never gets it.

Am I easier just filtering Edinburgh and Rej then counting the results?

Thank you in advance!

[strongandlong]

You need a pivot table

[strongandlong]

Oops. That was a bit abrupt. A pivot table lets you summarise. So you could have a row for each university and a column for each result. And the put a count in the results.

[Passmethecrisps]

Oh really? Hmm. Will need to get googling

[OrcinusOrca]

exceljet.net/formula/count-if-two-criteria-match

That explains it better than I can. Whilst using COUNTIF and AND seems logical, it's not really the right way to do it, you just need more than one range on your COUNTIF.

[Passmethecrisps]

That is exactly how I had planned it strong so that sounds about right.

I think I recall teaching pivot tables about 15 years ago. I need to get revising

[Passmethecrisps]

Ah thank you orcinus

[Hechan]

Try =COUNTIFS(C4:C390,"EDINB",F4:F390,"REJ")

[Passmethecrisps]

I think that’s what I need hechan.

However, now people have turned my head with pivot tables I might try them both.

There are about 20 universities and each one has 7 possible offers.

[Hechan]

I would prob do up a table with universities as row labels and offer categories as column headers, then do a countifs referencing the relative row and column in each table cell. So if a2 is EDINB and b1 is REJ, then formula in b2 is =COUNTIFS(yourdatatab!$C$4:$C$390,$a2,yourdatatab!$F$4:$F$390,b$1). You should be able to copy/paste across the cells in the table and get a count for each combination

[Passmethecrisps]

That has worked hechan. Thanks so much. It seemed very simple in the end so thanks for pointing me in the right direction.

I am going to look at pivot tables as well

[Hechan]