Svgmassive
asked on
sumproduct if
i would like to have a formula that will perform a sumproduct if the header has like tom
IF(($O$4:$V$4 "<>*tom*"),
then perform the sumproduct function
SUMPRODUCT(--SUBSTITUTE(TE XT($O$8:$V $8,"0.00") ,".",":")) )
then a similar function IF(($O$4:$V$4 "*tom*"),
then perform the sumproduct function
SUMPRODUCT(--SUBSTITUTE(TE XT($O$8:$V $8,"0.00") ,".",":")) )
thanks
IF(($O$4:$V$4 "<>*tom*"),
then perform the sumproduct function
SUMPRODUCT(--SUBSTITUTE(TE
then a similar function IF(($O$4:$V$4 "*tom*"),
then perform the sumproduct function
SUMPRODUCT(--SUBSTITUTE(TE
thanks
ASKER
sum the range $O$8:$V$8 if $O$4:$V$4 not like tom
but it will have to substitute the "." with ":" in the range $O$8:$V$8
which it the SUBSTITUTE(TEXT($O$8:$V$8, "0.00"),". ",":") part of the sumproduct
SUMPRODUCT(--SUBSTITUTE(TE XT($O$8:$V $8,"0.00") ,".",":")) )
or
if $O$4:$V$4 like tom
but it will have to substitute the "." with ":" in the range $O$8:$V$8
which it the SUBSTITUTE(TEXT($O$8:$V$8,
SUMPRODUCT(--SUBSTITUTE(TE
or
if $O$4:$V$4 like tom
ASKER
the substitute convert the entries to a time format
You have not distinguished a difference between cells that are like tom and cells that are not. In each case you are summing cells $O$8:$V$8.
Kevin
Kevin
ASKER
it varies so we look in range $O$4:$V$4 to find any cell that has a value like "tom" ,then sum the matching cells $O$8:$V$8 after converting the values $O$8:$V$8 to timevalues
Perhaps this.
If row 4 contains "tom":
=SUMPRODUCT((NOT(ISERROR(S EARCH("*to m*",O4:V4) )))*SUBSTI TUTE(O8:V8 ,".",":"))
If row 4 does not contain "tom":
=SUMPRODUCT((ISERROR(SEARC H("*tom*", O4:V4)))*S UBSTITUTE( O8:V8,".", ":"))
Kevin
If row 4 contains "tom":
=SUMPRODUCT((NOT(ISERROR(S
If row 4 does not contain "tom":
=SUMPRODUCT((ISERROR(SEARC
Kevin
What I provided above is a solution for this request:
Provide a formula to sum the values in O8:V8 if the value in the same column in row 4 contains "tom", and a second formula to do the same if the value in row for does not contain "tom". The values are times but with periods versus colons.
Kevin
Provide a formula to sum the values in O8:V8 if the value in the same column in row 4 contains "tom", and a second formula to do the same if the value in row for does not contain "tom". The values are times but with periods versus colons.
Kevin
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
If O8:V8 contain only numbers like 0.23, then my two suggestions will give the same answer. If there is a number like 1.23, then I suspect the SUMIF will give what you might consider a wrong answer.
The fix for Kevin's formula with the blank cell problem is to add 0 to O8:V8
=SUMPRODUCT((NOT(ISERROR(S EARCH("tom ",O4:V4))) )*SUBSTITU TE(O8:V8+0 ,".",":"))
Note that if O8:V8 contain more than two digits after the decimal point, this formula will give a different answer than the one using TEXT.
The fix for Kevin's formula with the blank cell problem is to add 0 to O8:V8
=SUMPRODUCT((NOT(ISERROR(S
Note that if O8:V8 contain more than two digits after the decimal point, this formula will give a different answer than the one using TEXT.
ASKER
byundt your formula =SUMPRODUCT(ISNUMBER(SEARC H("tom",O4 :V4))*SUBS TITUTE(TEX T(O8:V8,"0 .00"),".", ":")) is the right on point for the header that has tom
for the header that does not includes tom nothing return the accurate number. thanks
for the header that does not includes tom nothing return the accurate number. thanks
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Svgmassive,
Could you please post a workbook showing some sample data and the expected result?
Depending on what your data looks like, there could be a simpler formula. I am particularly interested in knowing how many digits you have to the left and right of the decimal point, and whether there might be blank cells.
Brad
Could you please post a workbook showing some sample data and the expected result?
Depending on what your data looks like, there could be a simpler formula. I am particularly interested in knowing how many digits you have to the left and right of the decimal point, and whether there might be blank cells.
Brad
ASKER
=SUMPRODUCT(ISNUMBER(SEARC H("tom",O4 :V4))*SUBS TITUTE(TEX T(O8:V8,"0 .00"),".", ":"))
=SUMPRODUCT(ISERR(SEARCH(" tom",O4:V4 ))*SUBSTIT UTE(TEXT(O 8:V8,"0.00 "),".",":" ))
These formulas did the trick the last one thanks a million byundt
=SUMPRODUCT(ISERR(SEARCH("
These formulas did the trick the last one thanks a million byundt
ASKER
the solutions that i got were remarkable....thanks to all that contributed
Kevin