Countifs with a two column range possible?

albertc30

Well-known Member
Joined
May 7, 2012
Messages
1,091
Office Version
  1. 2019
Platform
  1. Windows
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
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
In your sample data there don't appear to be any cells in column B that contain FRE.

Hi Andrew.

That is correct.

The word FRE will only be visible in column A.

All other date I.E MIN, BAS, will be displayed in column B.

Thanks for helping.

Regards,

Albert
 
Upvote 0
I went about it this way;

=COUNTIFS(A10:A29,H4,C10:C29,F9)+COUNTIFS(B10:B29,H4,C10:C29,F9)

All things it seems to be working as intendend for now.

Being;

H4 = FRE and F9 = Y

Any more ideas?

Thank you.

Albert
 
Upvote 0
=COUNTIFS(A2:B6,"FRE",C2:D6,"N")

works fine on your example

Check for leading and trailing spaces in your data that maybe the issue

from the help


"Each additional range must have the same number of rows and columns as the criteria_range1 argument. The ranges do not have to be adjacent to each other."</STRONG>
 
Last edited:
Upvote 0
=COUNTIFS(A2:B6,"FRE",C2:D6,"N")

works fine on your example

Check for leading and trailing spaces in your data that maybe the issue

from the help


"Each additional range must have the same number of rows and columns as the criteria_range1 argument. The ranges do not have to be adjacent to each other."</STRONG>

Hi.

I have done as you mentioned and now it does work to an extent but not entirelly I'm afraid.

I am now filtering for FRE, return the correct value. But when I filter for any values on the B column it won't return the correct qt, it will only display a zero.

Thanks for helping.
 
Upvote 0
Andrew;

Even though your formula works flaulessly, I am trying to understand/grasp the concept.

So, I went in working it as the formula for sumproduct sugests but my end result was wrong.

I ended up with;
=SUMPRODUCT(((A10:A19=H4)+(B10:B19=H4))>0)*(C10:C19=F9)

Similer but it does not work.

I can see all the --.

Do you think you could break it down how it works and flows please?

Many thanks.

Albert
 
Upvote 0

Forum statistics

Threads
1,213,537
Messages
6,114,216
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top