Count if multiple criteria with corresponding rows

[markdwalls]

Hi All,

Please see below. I need a formula (in yellow cells) that will return how many times the number is counted as well as the corresponding row returning a "yes". So for example, cell BA3 should be 6. Bit stuck with this one so any help will be appreciated.

Thanks in advance,

Mark

 

[Joe4]

This can be done pretty easily using COUNTIFS:

=COUNTIFS($AR$3:$AR$13,AZ3,$AX$3:$AX$13,"Yes")

See here for more details: MS Excel: How to use the COUNTIFS Function (WS)

 

[Sufiyan97]

Is it you want

Book4
	AR	AS	AT	AU	AV	AW	AX	AY	AZ	BA
1	1	2	3	4	5	6	Yes		1	1 Yes
2	1	2					Yes		2	1 Yes
3	1						Yes		3	0 Yes
4	1	2	3	4	5	6			4	1
Sheet1

Cell Formulas
Range	Formula
BA1:BA4	BA1	=COUNTIF(AR1:AW1,AZ1)&" "&AX1

 

[Joe4]

If you need BA4 to look for the 2s in column AS, etc, then modify the formula like this:

=COUNTIFS(OFFSET($AR$3:$AR$13,0,ROW()-3),AZ3,$AX$3:$AX$13,"Yes")

Which should work as you copy it down (will keep shifting the column to check by 1 as you move down each row).

 

[markdwalls]

Hi everyone, thanks for the quick responses! I haven't explained so well. Let me try again.

I have a list of products & the ingredients that go in to those products. The ingredients go in to multiple products. This is spread across the row (as shown below).
I then have columns which show whether these are used in certain areas, with the area as the heading & marked with "yes" or "no".
I need to count the amount of times each ingredient is used in each area.
I'm looking for a formula to go in to the yellow cells (I have entered the data manually for the example).

I'm not sure if I've explained this so well, but thank you to anyone who may be able to help!

 

[Joe4]

Hi everyone, thanks for the quick responses! I haven't explained so well. Let me try again.

I have a list of products & the ingredients that go in to those products. The ingredients go in to multiple products. This is spread across the row (as shown below).
I then have columns which show whether these are used in certain areas, with the area as the heading & marked with "yes" or "no".
I need to count the amount of times each ingredient is used in each area.
I'm looking for a formula to go in to the yellow cells (I have entered the data manually for the example).

I'm not sure if I've explained this so well, but thank you to anyone who may be able to help!

You have learned something very important here.
NEVER oversimplify your problem for the sake of posting it here (details are crucial).
Otherwise, you may get solutions that correctly answer the exact question you asked, but do not solve the real problem you have!

As it is, I am unable to modify my replies, which worked for the original question you asked, to work for the new details.
It would be easier if we were working with a normalized set of data (i.e., only one ingredient field, with each ingredient a separate row).

 

[Sufiyan97]

If possible modify your data like below and see if this works add each area before ingredient

Book2
	A	B	C	D	E	F	G
1	Item	Area	Ingredients		Ingredient	Retail Park	High Street
2	CHICKEN BURGER	Retail Park	MAYONNAISE		MAYONNAISE	1	0
3	FISH FINGER SANDWICH	Retail Park	CHICKEN FILLET		CHICKEN FILLET	1	0
4		High Street	BURGER BUN		BURGER BUN	0	1
5		High Street	SALAD		SALAD	0	1
6		Retail Park	GHERKIN		GHERKIN	1	0
7		Retail Park	COLESLAW		COLESLAW	1	0
8		High Street	FISH FINGERS		FISH FINGERS	0	1
9		High Street	BREAD		BREAD	0	1
Sheet1

Cell Formulas
Range	Formula
F2:F9	F2	=COUNTIFS($B$2:$B$50,$F$1,$C$2:$C$50,E2)
G2:G9	G2	=COUNTIFS($B$2:$B$50,$G$1,$C$2:$C$50,E2)

 

[Fluff]

How about

=SUMPRODUCT(($B$2:$G$3=$M2)*(H$2:H$3="Yes"))

 

[Joe4]

Thanks for the assist, fluff.
I swear, I tried something like that, but was not able to get it to work right.
Not sure what I was doing wrong. Maybe I had a typo somewhere.

 

[markdwalls]

How about

=SUMPRODUCT(($B$2:$G$3=$M2)*(H$2:H$3="Yes"))

Hi Fluff. Thanks so much!!!! This has worked You have made my day!