Hi Everyone,
I created a simple table to illustrate the data I have in my original workbook. In my workbook, this table is pulled from another sheet using VLOOKUP. The values are quantity purchased by each company. "-" indicates the company did not purchase anything in said year for said product. "0" indicates the company did purchase said product, in a particular quarter of the year.
I would like to show in a new column the tally for each company. I want to write a formula that would spit out "2019Q4_A, 2019Q2_B, 2020Q2_B, 2020Q4_C" for ABC Inc. I don't know where to start. I've been looking up different functions and imagine this may be a combination of VLOOKUP, ISNUMBER and COUNTIF???
Any guidance is much appreciated!
I created a simple table to illustrate the data I have in my original workbook. In my workbook, this table is pulled from another sheet using VLOOKUP. The values are quantity purchased by each company. "-" indicates the company did not purchase anything in said year for said product. "0" indicates the company did purchase said product, in a particular quarter of the year.
I would like to show in a new column the tally for each company. I want to write a formula that would spit out "2019Q4_A, 2019Q2_B, 2020Q2_B, 2020Q4_C" for ABC Inc. I don't know where to start. I've been looking up different functions and imagine this may be a combination of VLOOKUP, ISNUMBER and COUNTIF???
Any guidance is much appreciated!
Book1.xlsx | |||||||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | V | W | X | Y | |||
1 | 2019Q1 Product A | 2019Q2 Product A | 2019Q3 Product A | 2019Q4 Product A | 2019Q1 Product B | 2019Q2 Product B | 2019Q3 Product B | 2019Q4 Product B | 2019Q1 Product C | 2019Q2 Product C | 2019Q3 Product C | 2019Q4 Product C | 2020Q1 Product A | 2020Q2 Product A | 2020Q3 Product A | 2020Q4 Product A | 2020Q1 Product B | 2020Q2 Product B | 2020Q3 Product B | 2020Q4 Product B | 2020Q1 Product C | 2020Q2 Product C | 2020Q3 Product C | 2020Q4 Product C | |||
2 | ABC Inc | 0 | 0 | 0 | 4 | 0 | 6 | 0 | 0 | - | - | - | - | - | - | - | - | 0 | 10 | 0 | 0 | 0 | 0 | 0 | 20 | ||
3 | DEF Corp | - | - | - | - | 1 | 0 | 0 | 2 | - | - | - | - | 1 | 0 | 0 | 0 | 0 | 0 | 5 | 0 | - | - | - | - | ||
4 | GHI Ltd | 10 | 0 | 0 | 0 | - | - | - | - | - | - | - | - | - | - | - | - | 0 | 10 | 0 | 0 | 0 | 0 | 50 | 0 | ||
5 | JKL Enterprise | 0 | 1 | 0 | 1 | 0 | 2 | 0 | 2 | - | - | - | - | 0 | 1 | 0 | 0 | 6 | 0 | 0 | 0 | - | - | - | - | ||
6 | MNO Ltd | - | - | - | - | - | - | - | - | - | - | - | - | 0 | 0 | 2 | 0 | 0 | 0 | 0 | 8 | - | - | - | - | ||
Sheet1 |