look up based on greeter than zero value and specific letters (can’t be done via formula,

Do you know it can't be done using a formula, or are you guessing?

Was that someone here? Are you suggesting that I'm not smart?

He may be wrong ...

What I can tell you is this: having images for the company names is a no-no - they cannot be read by Excel. There's some bad data layout here - what you want to do is probably very easy, but will need some slight changes. I don't think you should need VBA for this.

Starter in P4:

=SUM(INDEX((Shortages!B$2:B$5=N4)*(Shortages!C$2:C$5=O4)*(Shortages!F$2:H$5),0))

What else do you need? Tell me where to look.

I have to go now - got other things to do, sorry.

If you can clearly clarify what you are attempting to do - one step at a time - then I am sure someone can help you to do this with formulae. You are going to need to be much clearer, though, about what is meant to be happening.

As I have to go, I have called for help, but whoever steps in will need the clarification I've asked for, so you need to participate.

I cannot figure out from your file and your description what you are trying to do.

On the breakdown tab - I need a look up in the highlighted in red area

The highlighted red area on the Breakdown tab looks like this. Which cell(s) do you want to contain the result of your lookup:

	N	O	P
4	GRP(538)	K P ROSSELLO RED	26
5	LHL (538)	EIGER GREY	30

if there’s a number in column F,G, H on the shortages tab it retunes what it is

Returns what? Returns it where? Returns it based on what--what are the criteria for the lookup?

and sums the 3 coloured columns (currently manually done)

In Breakdown you show 30 for LHL (538), EIGER GRAY. Where are you getting 30?

Example
It needs to look for everything with letters K P… then report anything that greater than zero please see highlighted example in red.. But it reports exactly what it is the K P variant is

Shortages tab this pasted data. columns A – N data changes daily… no fixed data

Breakdown tab looks at data from S-AA on the shortages tab

None of this makes sense until we get the other questions answered.

To get both the values in P4 and P5 modify AliGW's formula to read*:

Formula:

Please Login or Register  to view this content.

*Denotes an array entered formula
1. Select cell P4
2. Paste the above formula into the formula bar
3. Simultaneously press the Ctrl, Shift and Enter keys
4. Drag the fill handle down to cell P5.
Note that the only non-zero value for LHL (538) EIGER GREY is 2 (in cell H41)
Let us know if you have any questions.

Going to make another attempt at filling in the values on the Breakdown sheet that are highlighted in red.
1. A helper column (O) which may be moved and/or hidden for aesthetic purposes, is added to the Shortages sheet and populated using:

Formula:

Please Login or Register  to view this content.

2. On the Breakdown sheet N4:N12 are populated using:

Formula:

Please Login or Register  to view this content.

3. O4:O12 are populated using:

Formula:

Please Login or Register  to view this content.

4. P4:P12 are populated using:

Formula:

Please Login or Register  to view this content.

Let us know if you have any questions.