SOLVED

Join 2 COUNTIF to become 1 COUNTIFS

Copper Contributor

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

@glennmckenna 

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.

@Riny_van_Eekelen 

 

Thanks for your answer, could I just overlap both ranges ?

For example : K2:ZZ300 ?

@glennmckenna No you may not.

@Riny_van_Eekelen 

 

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 :

glennmckenna_0-1703161780186.png

 

best response confirmed by glennmckenna (Copper Contributor)
Solution

@glennmckenna 

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:

Riny_van_Eekelen_0-1703162566742.png

 

 

 

 

 

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"

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

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))

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

Many thanks, that is exactly what i was after

@glennmckenna Glad we figured it out.

@Riny_van_Eekelen 

 

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) ?

@glennmckenna 

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

@glennmckenna 

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:

Riny_van_Eekelen_0-1703162566742.png

 

 

 

 

 

View solution in original post