How to include a CountIf that is column dependent?

[helpneeded2]

I am not sure how to go forward on this sheet. (I am using Excel 365).

1. I have several columns of locations, lets call them: Store 1, Store 2, and Store 3.
2. Each column has several items, lets just call them: Fruit, Vegs, Meat.

On a different sheet, have a column that contains each of the Store names:

Column A;
Store 1
Store 2
Store 3

Now Column B will contain the total of all Fruits and Vegs (not Meat) for each location.

I'm not sure how combine the conditional countif (Fruit or Vegs) with what I am assuming would be a lookup or index?

Any guidance would be appreciated.

Thank you.

 

[Jeffrey Mahoney]

Try Countifs. Multiple column criteria

 

[helpneeded2]

The problem is, the countifs, still has to know where to go looking for the correct Store data.

In example:

On my results table, B2 should reference that since Store 1 will be column 1 of the lookup table/index, and then do the countifs.

 

[Jeffrey Mahoney]

Please try this.

Book7
	A	B	C
1	Store 1	Store 2	Store 3
2	Fruit	Vegs	Meat
3	Vegs	Vegs	Meat
4	Meat	Vegs	Meat
5	Fruit	Vegs	Meat
6	Fruit	Vegs	Meat
7	Fruit	Vegs	Meat
8	Fruit	Vegs	Meat
9	Fruit	Vegs	Meat
10	Vegs	Vegs	Meat
11	Vegs	Fruit	Meat
12	Meat	Fruit	Meat
13	Meat	Fruit	Meat
14	Meat	Fruit	Meat
Sheet1

Book7
	A	B	C	D
1	Store Name	Fruit	Vegs	Meat
2	Store 1	6	3	4
3	Store 2	4	9	0
4	Store 3	0	0	13
Sheet2

Cell Formulas
Range	Formula
A2:A4	A2	=TRANSPOSE(UNIQUE(Sheet1!A1:C1))
B2:D4	B2	=COUNTIF(OFFSET(Sheet1!$A$1,1,MATCH(Sheet2!$A2,Sheet1!$A$1:$R$1,0)-1,1000,1),Sheet2!B$1)
Dynamic array formulas.

 

[Jeffrey Mahoney]

You should strongly consider setting up your data into a database style format. It is easier to quantify.

Book7
	G	H
1	Store Name	Product
2	Store 1	Fruit
3	Store 1	Vegs
4	Store 1	Meat
5	Store 1	Fruit
6	Store 1	Fruit
7	Store 1	Fruit
8	Store 1	Fruit
9	Store 1	Fruit
10	Store 1	Vegs
11	Store 1	Vegs
12	Store 1	Meat
13	Store 1	Meat
14	Store 1	Meat
15	Store 2	Vegs
16	Store 2	Vegs
17	Store 2	Vegs
18	Store 2	Vegs
19	Store 2	Vegs
20	Store 2	Vegs
21	Store 2	Vegs
22	Store 2	Vegs
23	Store 2	Vegs
24	Store 2	Fruit
25	Store 2	Fruit
26	Store 2	Fruit
27	Store 2	Fruit
28	Store 3	Meat
29	Store 3	Meat
30	Store 3	Meat
31	Store 3	Meat
32	Store 3	Meat
33	Store 3	Meat
34	Store 3	Meat
35	Store 3	Meat
36	Store 3	Meat
37	Store 3	Meat
38	Store 3	Meat
39	Store 3	Meat
40	Store 3	Meat
Sheet1

 

[helpneeded2]

Thanks for your help, but I just can't get this to work the way I need it. Part of the problem is, that it is a very large spreadsheet which contains a lot of data, and I am trying to simplify it in my examples.

Here's the two conditions I am trying to capture in a single countifs:

=COUNTIF(INDEX('Raw Data'!$AP$2:$BR$12696,,MATCH($J2,'Raw Data'!$AP$1:$BR$1,0)),">"&0)
=COUNTIF('Raw Data'!AJ:AJ,"High") + COUNTIF('Raw Data'!AJ:AJ,"POI")

Here are the components:

Column J is my column of lookup header values from the columns AP to BR on "Raw Data". (It is not a formatted table, nor can it be which is why I am not using Headers).

For each row in J, I want to count the matches in the corresponding rows in the single matched column in AP to BR, however I only want it to do this if the value in column AJ on the related row is displaying either "High" or "POI".

 

[helpneeded2]

Please try this.
=TRANSPOSE(UNIQUE(Sheet1!A1:C1))[/XD][/XR][XR][XD]B2:D4[/XD][XD=fw:b]B2[/XD][XD]=COUNTIF(OFFSET(Sheet1!$A$1,1,MATCH(Sheet2!$A2,Sheet1!$A$1:$R$1,0)-1,1000,1),Sheet2!B$1)[/XD][/XR][/RANGE]

I finally figured out how to get this to work for my data. The OFFSET was key, and making sure it referenced the same number of max rows as my other countif references. Thanks for pointing me in the right direction.

(I also did not know of the TRANSPOSE function. I have been transposing through a complicated INDEX method. Thanks also for that.)

 

[Jeffrey Mahoney]

If I read you right: You want a count of values above Zero and either have "High" or "POI" in column AJ. Please try this

Book2
	J	K
1	Store Name
2	Store 1	3
3	Store 2	4
4	Store 3	0
Sheet1

Cell Formulas
Range	Formula
K2:K4	K2	=LET(Rng,INDEX('Raw Data'!$AP$2:$BR$12696,,MATCH(Sheet1!$J2,'Raw Data'!$AP$1:$BR$1,0)),SUM((IFERROR(Rng*1,0)>0)*(('Raw Data'!$AJ$2:$AJ$12696="High")+('Raw Data'!$AJ$2:$AJ$12696="POI"))))