SOLVED

# Join 2 COUNTIF to become 1 COUNTIFS

Copper Contributor

# Join 2 COUNTIF to become 1 COUNTIFS

Hello,

I've got 2 COUNTIF function that work fine how ever i would like to combine them to become 1 COUNTIFS

The 2 COUNTIF work fine by themselves and are as follow :

NB.SI(OIL!2:2;KPI!A2)
NB.SI(OIL!K4:ZZ300;KPI!F1)

and the COUNTIFS that is try to use is as follows

NB.SI.ENS(OIL!2:2;KPI!A2;OIL!K4:ZZ300;KPI!F1)

When i try this function i get #VALUE error ... could you please help me with this error ?

Ps. :

• NB.SI is the french translation of COUNTIF
• NB.SI.ENS is the french translation of COUNTIFS
• I'm using french EXCEL
13 Replies

# Re: Join 2 COUNTIF to become 1 COUNTIFS

COUNTIFS requires all criteria ranges to be the same size and dimension. So, you can't use both the entire row 2 and K4:ZZ300 as criteria ranges.

# Re: Join 2 COUNTIF to become 1 COUNTIFS

For example : K2:ZZ300 ?

# Re: Join 2 COUNTIF to become 1 COUNTIFS

@glennmckenna No you may not.

# Re: Join 2 COUNTIF to become 1 COUNTIFS

Do you have another solution ?

What i'm trying to do is get excel to find the columns where the values match a certain date/value (these values are all on 1 line) and the look in those columns and count the values that match "456"

I've can't use a Pivot table

here is an example of the excel :

best response confirmed by glennmckenna (Copper Contributor)
Solution

# Re: Join 2 COUNTIF to become 1 COUNTIFS

Can you give an example of what you would want to be returned?

For instance, would you ask how many '456' exist in columns for 21-dec-2023? The answer would be 2.

Would there really by duplicate dates in row 1?

Which Excel version are you using?

Perhaps something like this:

# Re: Join 2 COUNTIF to become 1 COUNTIFS

I'm using O365 enterprise

In the example i used dates but in reality it will be week numbers therefor there might be duplicates

what i'm looking for is indeed how many "456" exist in a column, for example for column 22/12/2023 i want excel to tell me that there is 3 instances of "456"

# Re: Join 2 COUNTIF to become 1 COUNTIFS

@glennmckenna I added a screenshot to my previous post, perhaps just after you read it. It that what you wanted?

# Re: Join 2 COUNTIF to become 1 COUNTIFS

That was indeed the case, iv'e got it to work in the example that we've shared it looks like it's what i'm after

however when i try it in the real excel i get #N/A

here is the function
=SOMME((OIL!3:3=KPI!A2)*(OIL!K4:ZZ300=KPI!F\$1))

# Re: Join 2 COUNTIF to become 1 COUNTIFS

@glennmckenna You can't use a reference to the entire row 3. You should limit it to columns K to ZZ

# Re: Join 2 COUNTIF to become 1 COUNTIFS

Many thanks, that is exactly what i was after

# Re: Join 2 COUNTIF to become 1 COUNTIFS

@glennmckenna Glad we figured it out.

# Re: Join 2 COUNTIF to become 1 COUNTIFS

Hello, i've just realised that i missed info in my initial question.

in reality the cells might not contain only "123" or "456" but they might contain for example "123 xyz"

is there a way to get this to work from a point when the cell contains the right info (adding stars for example "*123*" doesn't work) ?

# Re: Join 2 COUNTIF to become 1 COUNTIFS

Back to previous sample it could be

=SUMPRODUCT( --ISNUMBER( SEARCH(B13,A2:D9) )*(A1:D1=A14) )
1 best response

Accepted Solutions
best response confirmed by glennmckenna (Copper Contributor)
Solution

# Re: Join 2 COUNTIF to become 1 COUNTIFS

Can you give an example of what you would want to be returned?

For instance, would you ask how many '456' exist in columns for 21-dec-2023? The answer would be 2.

Would there really by duplicate dates in row 1?

Which Excel version are you using?

Perhaps something like this: