Countifs with a two column range possible?

[albertc30]

Morning to all.

I am trying to have a cell to return how many times has it been seen the word FRE within two columns range and then match a second criteria for "Y".

I can do it if the criteria range 1 is only one column based, but it won't work with two.

My data is as this;

* - A - B - C
1 -FRE - MIN - Y
2 -FRE - MIN - N
3 - - COM - Y
4 - - COM -Y
5 - FRE - BAS - N


As example above, I want the countifs to look for "FRE" within Column A & B and retun how many "Y". In this case would be 1.

Countifs function doesn't seem to like to look at two colomns for the range.

I need it this way as MIN belongs to class FRE and COM is its own range. So I wil also look for "MIN".

Any help much appreciated.

Many thansk for all the support.

Regards,
Albert

 

[albertc30]

Andrew;

From what I have seen the (--((A10:A19=H4)+(B10:B19=H4))>0) is the first array and the --(C10:C19=F9) is the second array.

Cannot come to a conclusion as to the -- signs in the formula.

P.S: and the > sign is to only check to cells with a value higher than 0.

 

[Andrew Poulsom]

The -- converts TRUE/FALSE into 1/0 so that SUMPRODUCT can do its multiplication.

The + sign is used for OR. If either or both cells contain the criteria, adding the results of the logical tests would return a number greater than zero (1 or 2).

Use the Evaluate Formula tool too see how it works.

If you want to use explicit multiplication the formula would be:

=SUMPRODUCT((((A10:A29=H4)+(B10:B29=H4))>0)*(C10:C29=F9))

 

[albertc30]

The -- converts TRUE/FALSE into 1/0 so that SUMPRODUCT can do its multiplication.

The + sign is used for OR. If either or both cells contain the criteria, adding the results of the logical tests would return a number greater than zero (1 or 2).

Use the Evaluate Formula tool too see how it works.

If you want to use explicit multiplication the formula would be:

=SUMPRODUCT((((A10:A29=H4)+(B10:B29=H4))>0)*(C10:C29=F9))

Much, much appreciated Andrew.

This can only come with years of experience.

I am still miles and years from this kind of knowledge.

Many thanks for explainning it mate and for taking the time to help.

Regards,
Albert

P.S: I must say, this was the first time I have come across this, I think.