# Excel help - count if

(12 Posts)
Passmethecrisps Tue 23-Oct-18 19:59:18

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!

OP’s posts: |
strongandlong Tue 23-Oct-18 20:00:14

You need a pivot table

strongandlong Tue 23-Oct-18 20:01:38

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 Tue 23-Oct-18 20:01:51

Oh really? Hmm. Will need to get googling

OP’s posts: |
OrcinusOrca Tue 23-Oct-18 20:02:52

https://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 Tue 23-Oct-18 20:02:56

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

OP’s posts: |
Passmethecrisps Tue 23-Oct-18 20:03:20

Ah thank you orcinus

OP’s posts: |
Hechan Tue 23-Oct-18 20:04:16

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

Passmethecrisps Tue 23-Oct-18 20:08:46

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.

OP’s posts: |
Hechan Tue 23-Oct-18 20:25:17

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 Tue 23-Oct-18 20:41:30

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

OP’s posts: |
Hechan Tue 23-Oct-18 20:54:33

Join the discussion

To comment on this thread you need to create a Mumsnet account.