Text string in SUMIF

Trying to get the formula to include for text.


=IF(D6<>"",SUMIF(K$2:U$2,"Yes",K6:U6),"")

So if K6:U6 contains text instead of a number this will be returned in the cell

I have manually input the "Nil" value highlighted in red

See where you are coming from.

The result needs to be in cell G6 taken from the SUMIF. Basically extending the formula in cell G5 to include the text option.

Unless I'm doing something wrong the HLOOKUP does not give me the flexibility.

The test string is a variable.

If any column within cell range $H$2;$R$2 contains "Yes" and any cell intersecting that column contains either a numerical value or text string then that value is returned in the "Selected" column

Please Select the yellow cells for Validation text

Originally Posted by daddylonglegs

OK, try this version in G6 copied down

=HLOOKUP("yes",H$2:R6,ROWS(H$2:R6),0)

Works with "Nil" but no other variables.
Column A can has a lot of cells unpopulated hence the reason for my original formula with the "" cell exclusion.

Originally Posted by oeldere

Maybe like this.

See the green cells.

Unfortunately this sums the entire row.
Please see previous response.

Originally Posted by SDCh

Hope this help you:
in column G :

Please Login or Register  to view this content.

in column B:

Please Login or Register  to view this content.

Please see the enclosed file with notes.
The result in column B is dependant upon where "Yes" is placed in Row $H$2:$R$2.
If "Yes" was placed in Column I then cell B9 should return 42.00
If "Yes" was placed in Column H then cell B9 should return "Excl"

I think the confusion has come where "Yes" was placed in $G$2. This value was originally a control value only.(I have now removed it)

OK, I confused in here, actually what you want with column B?

If you only want get the value between column where "Yes" are intersect with row Item then this the code:

Please Login or Register  to view this content.

Why I confused, because on your first post column B has formula:

Please Login or Register  to view this content.

As you see, you want calculate column G, so what happen if the value of column G are "NIL" or "Excl"?

B9 is the sum of F9*G9 if E9 is blank.
Else IF E9 ISNUMBER then E9*G9.

Basically the Directors adjustment in £ overides the Directors adjustment in %

Hope I'm not confusing things too much here.

I change my code on column B:

Please Login or Register  to view this content.

As you see, if G9 ="NIL" or G9="Excl" it will take whatever the value that match on intersection.

By the way, formula on column G is like you want?

Gents thanks for your help!

SDCh, Thank you, for showing me the light!!
Final [SOLVED] draft enclosed