Sumproduct where column headings and row values are criteria, including right function

[ASTOCH]

I received a massive file from a friend who has done extensive research and has been counting her results manually. We need to know for each subject (row) in the below example:
Case 1. How many LIVING objects e.g. Dog or Mouse the subject answered incorrectly. (Column B = "L" and Column C =1) and (Column F = "L" and Column G = 1).
Case 2. Same as above for NONLIVING objects. (Column D = "N" and Column E = 1).

I show case 2 just for illustrative purposes... If we solve case 1, we solve the total problem.

The file is huge so I am trying to be as efficient as possible by using something like:
=SUMPRODUCT(((Data!1:1="LivNonLiv")*(Data!2:2="L"))*((RIGHT(Data!1:1,3)="err")*(Data!2:2=1)))

I am able to get the correct results when I separate the formula:
=SUMPRODUCT((Data!1:1="LivNonLiv")*(Data!2:2="L")) returns the value 2 and
=SUMPRODUCT((RIGHT(Data!1:1,3)="err")*(Data!2:2=1)) returns the value 2 for subject 1

But then always get zero when I bring them together.

Col A	Col B	Col C	Col D	Col E	Col F	Col G
Subject	LivNonLiv	DogErr	LivNonLiv	HouseErr	LivNonLiv	MouseErr
1	L	1	N	0	L	1
2	L	0	N	0	L	1
3	L	1	N	1	L	0
4	L	0	N	1	L	0
5	L	0	N	1	L	0

<tbody>
</tbody>

 

[Aladin Akyurek]

Care to post the expected value (count) for Case 1 and the same for Case 2?

 

[ASTOCH]

Care to post the expected value (count) for Case 1 and the same for Case 2?

Hi Aladin, thanks for the enquiry response. Here are the case values.

Subject	LivNonLiv	DogCatErr	LivNonLiv	MountainHouseErr	LivNonLiv	MouseErr	Case 1	Case 2
1	L	1	N	0	L	1	2	0
2	L	0	N	0	L	1	1	0
3	L	1	N	1	L	0	1	1
4	L	0	N	1	L	0	0	1
5	L	0	N	1	L	0	0	1

<tbody>
</tbody>

 

[Aladin Akyurek]

Hi Aladin, thanks for the enquiry response. Here are the case values.

Subject	LivNonLiv	DogCatErr	LivNonLiv	MountainHouseErr	LivNonLiv	MouseErr	Case 1	Case 2
1	L	1	N	0	L	1	2	0
2	L	0	N	0	L	1	1	0
3	L	1	N	1	L	0	1	1
4	L	0	N	1	L	0	0	1
5	L	0	N	1	L	0	0	1

<tbody>
</tbody>

Thanks...

Can you work with the set up that follows?

In H2 enter and copy down:

=SUMPRODUCT(--($B2:$F2="L"),--($C2:$G2=1))

In I2 enter and copy down:

=SUMPRODUCT(--($B2:$F2="N"),--($C2:$G2=1))

 

[ASTOCH]

Hi Aladin,

Thank you for your suggestion but that will not solve the issue. I have pasted in an extract from the actual spreadsheet to explain why this will not work. But in summary, we can only count where LivNonLiv = "L" and the right 3 chars in the 5th column after = "err".

SubjID	Desk	LivNonLiv	DeskLess2	DeskGr2	DeskQ	DeskTOT	DeskErr	DeskOther	Pencil	LivNonLiv	PencilLess2	PencilGr2	PencilQ	PencilTOT	PencilErr	PencilOther	Plant	LivNonLiv	PlantLess2	PlantGr2	PlantQ	PlantTOT	PlantErr	PlantOther	Bookstore	LivNonLiv	BookLess2	BookGr2	BookQ	BookTOT	BookErr	BookOther	Weed	LivNonLiv	WeedLess2	WeedGr2	WeedQ	WeedTOT	WeedErr	WeedOther	Yacht	LivNonLiv	yachtLess2	yachtGr2	yachtQ	yachtTOT	yachtErr	yachtOther	Bird	LivNonLiv	BirdLess2	BirdGr2	BirdQ	BirdTOT	BirdErr	BirdOther	Tree	LivNonLiv	TreeLess2	TreeGr2	TreeQ	TreeTOT	TreeErr	TreeOther	Raspberry	LivNonLiv	RaspLess2	RaspGr2	RaspQ	RaspTOT	RaspErr	RaspOther	Writer	LivNonLiv	WriterLess2	WriterGr2	WriterQ	WriterTOT	WriterErr	WriterOther	Binoculars	LivNonLiv	BinoLess2	BinoGr2	BinoQ	BinoTOT	BinoErr	BinoOther	Knee	LivNonLiv	KneeLess2	KneeGr2	KneeQ	KneeTOT	KneeErr	KneeOther	Harmonica	LivNonLiv	HarmLess2	HarmGr2	HarmQ	HarmTOT	HarmErr	HarmOther	Hostess	LivNonLiv	HostLess2	HostGr2	HostQ	HostTOT	HostErr	HostOther	Sticker	LivNonLiv	StickerLess2	StickerGr2	StickerQ	StickerTOT	StickerErr	StickerOther	Shirt	LivNonLiv	ShirtLess2	ShirtGr2	ShirtQ
1234	84	NL	1	0	0	0	0	0	150	NL	1	0	0	0	0	0	88	L	1	0	0	0	0	0	78	NL	1	0	0	0	0	0	353	L	0	1	0	1	0	lawn	141	NL	1	0	0	0	0	0	318	L	0	1	0	1	0	0	297	L	0	1	0	1	0	0	66	NL	1	0	0	0	0	0	113	L	1	0	0	0	0	0	104	NL	1	0	0	0	0	0	106	L	1	0	0	0	0	0	250	NL	0	1	0	1	0	0	138	L	1	0	0	0	0	0	147	NL	1	0	0	0	0	0	556	NL	0	1	0
151235	59	NL	1	0	0	0	0	0	1970	NL	0	1	0	1	0	sandwich, fruit, wine, cheese	37	L	1	0	0	0	0	0	59	NL	1	0	0	0	0	0	419	L	0	1	0	1	0	shrubs	57	NL	1	0	0	0	0	0	1193	L	0	1	0	1	0	mockingbird, minabird	85	L	1	0	0	0	0	0	60	NL	1	0	0	0	0	0	69	L	1	0	0	0	0	0	53	NL	1	0	0	0	0	0	57	L	1	0	0	0	0	0	116	NL	1	0	0	0	0	0	72	L	1	0	0	0	0	0	56	NL	1	0	0	0	0	0	88	NL	1	0	0
23462	97	NL	1	0	0	0	0	0	53	NL	1	0	0	0	0	0	125	L	1	0	0	0	0	0	130	NL	1	0	0	0	0	0	351	L	0	1	0	1	0	0	65	NL	1	0	0	0	0	0	122	L	1	0	0	0	0	0	163	L	1	0	0	0	0	0	100	NL	1	0	0	0	0	0	50	L	1	0	0	0	0	0	147	NL	1	0	0	0	0	0	50	L	1	0	0	0	0	0	403	NL	0	1	0	1	0	0	116	L	1	0	0	0	0	0	72	NL	1	0	0	0	0	0	103	NL	1	0	0
23451	56	NL	1	0	0	0	0	0	47	NL	1	0	0	0	0	0	63	L	1	0	0	0	0	0	75	NL	1	0	0	0	0	0	84	L	1	0	0	0	0	0	59	NL	1	0	0	0	0	0	219	L	0	1	0	1	0	0	85	L	1	0	0	0	0	0	59	NL	1	0	0	0	0	0	44	L	1	0	0	0	0	0	63	NL	1	0	0	0	0	0	53	L	1	0	0	0	0	0	56	NL	1	0	0	0	0	0	91	L	1	0	0	0	0	0	75	NL	1	0	0	0	0	0	81	NL	1	0	0
123415	97	NL	1	0	0	0	0	0	109	NL	1	0	0	0	0	0	47	L	1	0	0	0	0	0	53	NL	1	0	0	0	0	0	87	L	1	0	0	0	0	0	62	NL	1	0	0	0	0	0	175	L	1	0	0	0	0	0	162	L	1	0	0	0	0	0	47	NL	1	0	0	0	0	0	84	L	1	0	0	0	0	0	60	NL	1	0	0	0	0	0	34	L	1	0	0	0	0	0	125	NL	1	0	0	0	0	0	84	L	1	0	0	0	0	0	72	NL	1	0	0	0	0	0	94	NL	1	0	0

<tbody>
</tbody>

 

[ASTOCH]

Hi Aladin,

After reading my last post i realized it would be confusing. To clarify, the formula should count:
where LivNonLiv = "L" and
where the 5th column after (should be where right 3 chars is "err") = "1"

So, the sumproduct formula needs to see 4 conditions structured like:
1. Where the column heading is LivNonLiv and
2. where the row value = "L"
and
3. where the right 3 characters of the column heading = "err" and
4. where the row value =1

Thanks again



Hopefully this clarifies.

 

[Aladin Akyurek]

Hope you can work with what follows...

L >>

=SUMPRODUCT(--(C2:DS2="L"),--(H2:DX2=1))

NL >>

=SUMPRODUCT(--(C2:DS2="NL"),--(H2:DX2=1))