Counting function with multiple criterias

[etaver87]

Hi everyone,

I have a table with 6 columns and I need to count entries in column A, when certain criterias are met. I'll try to attach an example if it works.

I have two steps for which I need a formula:

1. I need a formula that counts a project in column A when he finds a certain number in column C which is not on the same row in column E. If he finds e.g. 46982 in column C and E he should not count the project in A

2. I need a formula that counts a project in column A when he finds a certain number in column C and on the same row finds that number in column E too. (So basically, what Excel doesn't count in 1. it should count here).

[jason.b75]

For step 1

=COUNTIFS(A:A,A2,C:C,"<>"&E2)

Step 2

=COUNTIFS(A:A,A2,C:C,E2)

[etaver87]

Many thanks for your help.

However, it is not exactly what I am looking for.

This works fine if I want to count for every row separately.

In my case, I want to insert a formula into one cell only and then it should count all the unique project numbers in column A with the criterias in mind.

[JohnTopley]

Deleted by JT

[etaver87]

OK, this doesn't work exactly as expected. I think I can figure out so that it gives me the sum count of all unique project numbers for a certain number in column C.

The difficult thing for me is now, however, to make sure, that it doesn't count the four cases for number 46982 where that person is also in the Manager role, i.e. where the number 46982 is also in column E.

So I first would want the sum count of all unique projects that person is on, e.g. person 46982, and then deduct from that sum count the cases where that person is on both Director and Manager role (four cases here, cf. screenshot)

[jason.b75]

Attached png images don't display correctly on the forum, so we can't see your latest example.

A couple of things to try and make this work more efficiently.

Can we add an extra column with some helper formulas if needed?

How should rows with # unassigned be counted?

[etaver87]

Yes, absolutely. Some additional formulas, rows, columns are absolutely fine. Many thanks for your help!

[jason.b75]

And the # Unassigned rows, should they be counted or ignored?

[etaver87]

Fine if they are ignored.

[jason.b75]

Using your sample file from post #1, enter this formula into G2, and autofill down.

=IF(AND(C2<>"#",C2=E2),A2&"|"&C2,"")

Then do the same with this formula in H2.

=IF(AND(C2<>"#",C2<>E2),A2&"|"&C2,"")

Then use this Array formula to count the number of unique records where columns C and E have the same number

=SUM(1/COUNTIF(G2:G4219,G2:G4219))-1

And this one to count the number of unique records where columns C and E have different numbers

=SUM(1/COUNTIF(H2:H4219,H2:H4219))-1

Please note that Array Formulas must be confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. In the case of these formulas, if it is not done correctly then you will see a #DIV/0! error.

I have noticed that the array formulas are a little slow to calculate so will see if I can find an alternative to improve that.

[etaver87]

That is really helpful, thank you very much! I'm sure you can help me for my next step:

I would like to know how many projects there are in column A for the director, for example with the number 46982. How many projects is that person on, basically. Is that possible to count from your above computations? Probably it's really obvious but I don't see it right now.

[etaver87]

That is really helpful, thank you very much! I'm sure you can help me for my next step:

I would like to know how many projects there are in column A for the director, for example with the number 46982. How many projects is that person on, basically. Is that possible to count from your above computations? Probably it's really obvious but I don't see it right now.

[jason.b75]

A helper of =IF(E2=46982,A2,"") would be one way, then use an array formula in the same format as the others to get the count.

Might be possible with a pivottable if it's set up correctly.