Countif work Seperately Countifs doesn't

[Schoots]

Hello,

I'm having some troubles working with the COUNTIFS formula gathering information from the pivot table shown below. (it is actually larger but the idea is the same)

SupplierNr.	Descrption	Number	Problem
R00000162	ior 1503104. GEEN THT VERKEERDE MAAT SLECHT	M150830	03-KWALITE
R00000162	Een SO2 resultaat voldoet niet aan de EU-norm	M160606	01-VEILIG
R00000162	glas in pinda's. 1409210. reactie van Belfrudis	M141324	01-VEILIG
R00000162	steentje in pinda's. lot 15-28, tht 02/2016.	M150932	01-VEILIG
R00000162	teveel SO2 in rozijnen. Belfrudis klacht verstuurd	M140700	01-VEILIG
R00000173	teveel Chloraat in sample. Berrico klacht	M160030	01-VEILIG
R00000204	06-05 Hanos order niet geleverd op afgesproken dag	M150455	07-LOGISTI
R00000204	1 emmer schade Kaashuis Dorestad Wijk bij Duursted	M160577	07-LOGISTI
R00000204	2/3 juni te laat bij Rene Tiel om te laden	M150548	07-LOGISTI

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>

I need to be able to count for each SupplierNr. in the first collumn how many times a problem occurs.
So for R00000162 and 03-KWALITE that would be 1, for R00000162 and 01-VEILIG that would be 4 etc.

The easy way to do this is by making use of the COUNTIFS formula but this does not seem to work although the two seperate COUNTIF formulas do. and that is what i cannot seem to understand.

=countif(A1:A1000,R00000162) Works
=countif(D1:D1000,"03-LOGISTI") Works

=countifs(.... Doesn't work.

How is this possible and how can this be avoided? either with COUNTIFS or another (Array)formula

thanks in advance

 

[RoryA]

COUNTIFS should work - what exactly is happening when you use it?

You could of course simply create another pivot table to count for you.

 

[oldbrewer]

SupplierNr.	Descrption	Number	Problem
R00000162	ior 1503104. GEEN THT VERKEERDE MAAT SLECHT	M150830	03-KWALITE
R00000162	Een SO2 resultaat voldoet niet aan de EU-norm	M160606	01-VEILIG
R00000162	glas in pinda's. 1409210. reactie van Belfrudis	M141324	01-VEILIG
R00000162	steentje in pinda's. lot 15-28, tht 02/2016.	M150932	01-VEILIG
R00000162	teveel SO2 in rozijnen. Belfrudis klacht verstuurd	M140700	01-VEILIG
R00000173	teveel Chloraat in sample. Berrico klacht	M160030	01-VEILIG
R00000204	06-05 Hanos order niet geleverd op afgesproken dag	M150455	07-LOGISTI
R00000204	1 emmer schade Kaashuis Dorestad Wijk bij Duursted	M160577	07-LOGISTI
R00000204	2/3 juni te laat bij Rene Tiel om te laden	M150548	07-LOGISTI
		Count of Problem	Problem
		SupplierNr.	01-VEILIG	03-KWALITE	07-LOGISTI	Grand Total
		R00000162	4	1		5
		R00000173	1			1
		R00000204			3	3
		Grand Total	5	1	3	9
		another pivot table on the pivot table output ?

<colgroup><col><col><col><col span="3"><col><col span="3"></colgroup><tbody>
</tbody>

 

[Aladin Akyurek]

=COUNTIFS(A:A,"R00000162",D:D,"03-KWALITE")

=COUNTIFS(A:A,"R00000162",D:D,"01-VEILIG")

should work, but it's better that you apply these formulas on the source data or settle the matter entitely with a pivot approach.

 

[Schoots]

I also found it very strange that COUNTIFS would not work.
When i enter the formula i get a #VALUE! error.

The thing is that i do not want the end users of the worksheet to see the pivot table, they will be able to input the SupplierNr. and than get an overview which amongst other information includes the amount each problem arrised.

I did however not thought of just simply organizing the Pivot table in a different way which would, in combination with INDEX MATCH also give me the desired result.

Once again this forum has saved me!
Thank you all for the help.

 

[Schoots]

Gathering the information as described above would cause troubles when filtering the Pivot table on certain Dates.
Instead i've now used GETPIVOTDATA to get my desired results.