Count two values in different cells within the same row

[rhettblaine]

Hello all,

I'm trying to count how many times two different values appear in the same row and return those results in the same row. My obvious go to was COUNTIFS but that only works when it's one criteria; once I add an additional Criteria it will return 0. I've tried using SUMPRODUCT but receive the same results.

I also did not create the data source (A1 to H4 in my example below) so I'm having to get creative (I1:L4) so that I can create graphs, etc.

Due to the sensitivity of the data, I cannot post an exact replica of my table.

A

B

C

D

E

F

G

H

I

J

K

L

1

Customer

Order Date

Item 1

Status 1

Item 2

Status 2

Item 3

Status 3

T-Shirt

Pending

Delivered

Canceled

2

Customer 1

Nov 4, 2022

T-Shirt

Pending

Hoodie

Delivered

Hat

Canceled

1

1

0

0

3

Customer 2

Nov 5, 2022

T-Shirt

Delivered

T-Shirt

Pending

Hat

Pending

2

1

1

0

4

Customer 3

Dec 12, 2022

T-Shirt

Canceled

T-Shirt

Canceled

T-Shirt

Delivered

1

0

1

2

 

[Sufiyan97]

Hi can you please explain what you want to count?

 

[Fluff]

Is this what you want.

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
1	Customer	Order Date	Item 1	Status 1	Item 2	Status 2	Item 3	Status 3	T-Shirt	Pending	Delivered	Canceled
2	Customer 1	Nov 4, 2022	T-Shirt	Pending	Hoodie	Delivered	Hat	Canceled	1	1	0	0
3	Customer 2	Nov 5, 2022	T-Shirt	Delivered	T-Shirt	Pending	Hat	Pending	2	1	1	0
4	Customer 3	Dec 12, 2022	T-Shirt	Canceled	T-Shirt	Canceled	T-Shirt	Delivered	3	0	1	2
Lists

Cell Formulas
Range	Formula
I2:I4	I2	=COUNTIFS(C2:G2,I$1)
J2:L4	J2	=COUNTIFS($C2:$G2,$I$1,$D2:$H2,J$1)

 

[rhettblaine]

Is this what you want.

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
1	Customer	Order Date	Item 1	Status 1	Item 2	Status 2	Item 3	Status 3	T-Shirt	Pending	Delivered	Canceled
2	Customer 1	Nov 4, 2022	T-Shirt	Pending	Hoodie	Delivered	Hat	Canceled	1	1	0	0
3	Customer 2	Nov 5, 2022	T-Shirt	Delivered	T-Shirt	Pending	Hat	Pending	2	1	1	0
4	Customer 3	Dec 12, 2022	T-Shirt	Canceled	T-Shirt	Canceled	T-Shirt	Delivered	3	0	1	2
Lists

Cell Formulas
Range	Formula
I2:I4	I2	=COUNTIFS(C2:G2,I$1)
J2:L4	J2	=COUNTIFS($C2:$G2,$I$1,$D2:$H2,J$1)

Correct, that's the results that I'm expecting. I've tried that formula but I keep receiving 0 when there should be a count.

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Check that you don't have any leading/trailing spaces in the data.

 

[rhettblaine]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Check that you don't have any leading/trailing spaces in the data.

Thanks Fluff!

I've updated my info.

I'm using version 365.

I've also checked for leading/trailing spaces, they all match; spelling is correct; used Len also to make sure, all matches.

The table that contains the data is a connection to a SharePoint list. The columns where I'm placing the formulas are an extension of the table. So, using my example table above, A:H are from SharePoint, and I:L are extended parts of the table that contain the formula.

 

[Fluff]

Is the formula in the same workbook as the data?

 

[rhettblaine]

Is the formula in the same workbook as the data?

Yes, it is.

As long as I don't add additional criteria to the COUNTIFS formula it works:

=COUNTIFS($D2:$H2,J$1) (WORKS)

=COUNTIFS($C2:$G2,$I$1,$D2:$H2,J$1) (DOES NOT WORK)


Edit
I also just tried using it outside of a table. COUNTIFS still does returns a count of 0.

 

[Fluff]

Does this work

=COUNTIFS($C2:$G2,$I$1)

 

[rhettblaine]

Does this work

=COUNTIFS($C2:$G2,$I$1)

Yes, that works (I meant to type C2, not D2 in my last reply). The issue starts when I add the second criteria.