Using SUM with Find

[stojko89]

Hi,

What I'm tryn to do is check if and cell in a range contains the letter "P" and some value after "P" so like "P4".
If it does then SUM the value from this cell so I'm guessing the LEFT funcion should be used.
But I don't know how to combine this into a working formula
Please help.

Example is like this:
Search the range B4:AF4 if it contains a value "P" and some number after "P" then SUM those values.
But if it only contains "P" and no value after it then just skip it.

Cell value example:
D4 = "P"
E4 = "P2"
H4 = "P4"
So the sum of these values would be
AI4 = "6"

Hope someone understands this

[jraj1106]

Hi.

Try this function. Called the function in cell AI4:

temp.xlsm

[benishiryo]

hi stojko89, nice to see you again. an alternative:
=SUMPRODUCT((ISNUMBER(SEARCH("P",B4:AF4)))*(SUBSTITUTE(B4:AF4,"P",0)))

[amitkr3855]

In D5

=IFERROR(SUM($C5:C5,IFERROR(IF(SEARCH("P*",D4),RIGHT(D4,LEN(D4)-SEARCH("P",D4))+0),"")),"")

R's
Amit Kumar

[stojko89]

Hello,

Thanks for your answers but:
jraj1106 - I don't want to use a macro (don't know why) but if I use a macro for this I can also use it for every thing else and I don't want that lol (I'm strange about this some times hehe)
benishiryo - nice to see you again too but the code doesn't work gives me back an error.
amitkr3855 - youre code doesn't work either.

I'm thinking of a code that would go some how like this:
Find P in the range B4:AF4 and then sum the values by the P. The values are maximum 2 numbers.
So if values are like this for example:
D4 = "P"
E4 = "P2"
H4 = "P4"
O4 = "P10"

AI4 is the sum of these values so 16

[:) Sixthsense :)]

Array formula - Requires Ctrl+Shift+Enter

Please Login or Register  to view this content.

[stojko89]

Sixthsense thanks! that works
But could you explain this to me:

Please Login or Register  to view this content.

I know that Left Trim....checks if theres a "V" in the cell but whats with the *ISNUMBER?
ISNUMBER checks if the other trimmed part is a number that I get but why do you use the * between?

[:) Sixthsense :)]

@ stojko89,

Glad it helps you and thanks for the feedback

The trim function is used what happens if the data after P possess leading and trailing spaces like this

Please Login or Register  to view this content.

So using Trim will make the data like

Please Login or Register  to view this content.

using mid function =MID("P 25",2,255) will result the data as SPACE25 like this

Please Login or Register  to view this content.

using SPACE25*1 will convert it as real number

Please Login or Register  to view this content.

using the converted value isnumber(25) will check and ensure whether it is number (i.e.) isnumber("Abc")

Finally * operates as AND operator in ARRAY FORMULA'S.

Hope it is clear

[stojko89]

Ohhh ok thanks for the explenation and the help, sory I can't give you more that one * Rep.
If I could I would ask for one more thing tough?

[:) Sixthsense :)]

You are welcome and thanks for the rep

If your next question is something related to this thread then you can continue with this thread or create a new thread

[stojko89]

It's related becuse it contins the formula you just gave me with a different fix.
So I had my formula and I combined your formula with mine.
But since I have more text then just "P" I also have "V" and "H" I used the ISTEXT function.
And here is my formula now.
But the problem is that insted of getting the value 14 I get 15 when it counts my 4th row.

Please Login or Register  to view this content.

Any idias?

[:) Sixthsense :)]

I am not sure what the below conditions are doing and what is the purpose of these code's

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Have you entered it as array formula?

[stojko89]

It's entered as an array formula.
Here is the workbook I have.

i have explained inside what each column counts or should count or sum.

[stojko89]

So since the value was 15 instead of 14 I've tryed putting -1 in the formula and it works:

Please Login or Register  to view this content.

[stojko89]

So the formula like this:

Please Login or Register  to view this content.

Works fine now I Guess...unless you have any other idias

[stojko89]

Nah...it doesn't work correctly for other months just for this one

[:) Sixthsense :)]

I gone for lunch and now I am back now I will have a look in your file

[:) Sixthsense :)]

If you don't mind can you please show the expected output, because the column headers are in some other language so I am confused what the data's are and what needs to be arrived

[stojko89]

This is going to be hard to explain hehe but I'll try to writte it down. Give me 5min.

[stojko89]

Ok so here goes
Please let me know if you understand what I'm tryn to do.
Columns AG,AH and AJ work as they should.
Cell AK3 also works as it should the only column that I have problem with is AI for each row.
It should count like this:
All present hours (8,9,11,...) <---this is how many hours I'm present that day.
Plus all numbers after a letter (P8, P3, V3, V8,...)
Plust Vacations days * 8 so we get hours out of that
All that minus the hours that are possible present hours till that day <---- thats why I use the countblank cell function.

[stojko89]

Here is the short version of the story:
This part of the code:

Please Login or Register  to view this content.

Returns value 9 instead of 8 for cells B4:AF4.
It should retrun value 8 because the only cell in that rage that contains a number after the letter is I4 and the value after the letter is 8.

[:) Sixthsense :)]

This part is the error part of your formula ISTEXT(LEFT(

Left converts Numeric Value as Text Value the H4 cell value 11 has been converted into "11" because of left function. =Left(H4,1) result as "1" so it treated as text and the another 1 is getting added so it is resulting as 9 instead of 8.

[stojko89]

Haha! so I'm just using SUM(IF(ISTEXT(B4:AF4)*ISNUMBER and it works!!!
Yeiiii
Thanks a lot!

Now it's finaly solved hehe

[:) Sixthsense :)]

Glad it helps you and thanks for the feedback