Excel - COUNTIF?

[Passmethecrisps]

I was hoping someone could help me.

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?

Sorry - thank you in advance

Try this link: exceljet.net/formula/count-if-two-criteria-match

You should create a pivot table. Much easier than countifs

esme you are the second person to suggest pivot tables. I hedged my bets and tried in the staffroom section as well.

I have only vague recollections of using pivot tables so I will need to revise

That link looks helpful eggy.

It will be interesting to look at both methods

I think it's best to use COUNTIFS if you want to test two different things stored in two different columns.

If pivots aren't for you, add a column to add the cells with EDINB and REJ to give EDINBREJ (=cell1&cell2) and just COUNTIF on the combined column.

If you add another column and combine C&F it should work.

So in new column:

=C4&F4

This will bring back 'EDINBREJ' for example.

Then in the cell where you want the answer you'd do:

=COUNTIF(colG,"EDINBREJ")

(Assuming your new formula was in column G, obviously amend as appropriate).

And if you want to look up different unis then you can amend the formula depending, using a drop down rather than manually changing the formula. Happy to help more if you need it.

Thanks so much. I wasn’t expecting to get any replies.

There are roughly 20 institutions spread across 90 applicants with Max 5 applications each. Each application could have 7 possible offers. So I want to see easily how many of those who apply to, for example, Edinburgh, actually get an offer then drill down a bit.

How is the data spread out? Could you upload a photo so we can see what is in each column etc?
It will then be easier to give you actual formulae to use.

I am going to look at it at the moment jupiet

Ok. I have used a countifs statement and got it working for now.

I have had some great tips though so thank you for those. I will practice with pivot tables. What I want to do is have the spreadsheet set up properly so that if I leave someone else can carry on