Count if looking up to reference fields

[starfunker]

Hi all,

First time posting, sorry if I don't adhere 100% to the format but I'll try.

I have 2 sheets, sheet1 is a promotional grid with SKU codes in column 1, and 52 columns (weeks) to the right which will either be blank or have a promotional price for each sku code for that week.

SKU , wk1, wk2, wk3, wk4...
code1, $8 , , $6 , $8...
code2, $10, , , $10...

On sheet2 I have a column of SKU codes in let's say column1, and a different promotional price in columns1,2 & 3
and a count cell next to each of these columns which will tell me how many weeks of the year that SKU was promoted at that price.

SKU , Prom1, Count1, Prom2, Count2, Prom3, Count3
code1, $8 , 2 , $6 , 1 , $4 , 0
code2, $10, 2 , $8 , 0 ,(blank) ,(blank)
...

In sheet 2 I want to determine the figures in the Count columns by finding the matching SKU code in sheet1 then counting the instances along the row that the price in the Prom column appears.

The SKUs codes don't line up between sheet1 & sheet2 so I have tried to incorporate an array vlookup into a countif statement, e.g. =countif(vlookup(<sku code="">,$A:$AZ,{2,3,4,5,...},FALSE),
)

However the array vlookup seems to require a set of cells to output the values to, where as I want to store those values only for the purpose of of counting them as part of the formula.

Any help would be appreciated.

Thanks.</sku>

 

[starfunker]

I re-read the whole post, but screwed up the title... It's meant to say two reference fields. Uuurrgghh!

 

[Marcelo Branco]

Welcome to Mr Excel forum

If i understood correctly your data setup in Sheet1 is (adjust the sheet name if needed)

[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

A​

[/TD]
[TD="bgcolor: #DCE6F1"]

B
​

[/TD]
[TD="bgcolor: #DCE6F1"]

C​

[/TD]
[TD="bgcolor: #DCE6F1"]

D​

[/TD]
[TD="bgcolor: #DCE6F1"]

E​

[/TD]
[TD="bgcolor: #DCE6F1"]

F​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD]

SKU​

[/TD]
[TD]

wk1
​

[/TD]
[TD]

wk2​

[/TD]
[TD]

wk3​

[/TD]
[TD]

wk4​

[/TD]
[TD]

...​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD]

code1​

[/TD]
[TD]

8
​

[/TD]
[TD][/TD]
[TD]

6​

[/TD]
[TD]

8​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

3​

[/TD]
[TD]

code2​

[/TD]
[TD]

10
​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

10​

[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

With the 52 weeks in columns B:BA

If i'm right try this on another sheet

[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

A​

[/TD]
[TD="bgcolor: #DCE6F1"]

B
​

[/TD]
[TD="bgcolor: #DCE6F1"]

C​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD]

SKU​

[/TD]
[TD]

Prom. Price
​

[/TD]
[TD]

Count​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD]

code1​

[/TD]
[TD]

8​

[/TD]
[TD]

2​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

3​

[/TD]
[TD]

code1​

[/TD]
[TD]

6​

[/TD]
[TD]

1​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

4​

[/TD]
[TD]

code1​

[/TD]
[TD]

4​

[/TD]
[TD]

0​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

5
​

[/TD]
[TD]

code2​

[/TD]
[TD]

10​

[/TD]
[TD]

2​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

6
​

[/TD]
[TD]

code2​

[/TD]
[TD]

8​

[/TD]
[TD]

0​

[/TD]
[/TR]
</tbody>[/TABLE]

Formula in C2 copied down
=COUNTIF(INDEX(Sheet1!B:BA,MATCH(A2,Sheet1!A:A,0),0),B2)

Hope this helps

M.