Solved

# sumproduct if

Posted on 2013-06-29
508 Views
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(TEXT(\$O\$8:\$V\$8,"0.00"),".",":")))

then a similar function IF((\$O\$4:\$V\$4 "*tom*"),

then perform the sumproduct function
SUMPRODUCT(--SUBSTITUTE(TEXT(\$O\$8:\$V\$8,"0.00"),".",":")))

thanks
0
Question by:Svgmassive
• 6
• 4
• 4

LVL 81

Expert Comment

ID: 39287287
Please describe the function in English without the formulas.

Kevin
0

Author Comment

ID: 39287293
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(TEXT(\$O\$8:\$V\$8,"0.00"),".",":")))

or
if  \$O\$4:\$V\$4   like tom
0

Author Comment

ID: 39287297
the substitute convert the entries to a time format
0

LVL 81

Expert Comment

ID: 39287302
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
0

Author Comment

ID: 39287312
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
0

LVL 81

Expert Comment

ID: 39287313
Perhaps this.

If row 4 contains "tom":

=SUMPRODUCT((NOT(ISERROR(SEARCH("*tom*",O4:V4))))*SUBSTITUTE(O8:V8,".",":"))

If row 4 does not contain "tom":

=SUMPRODUCT((ISERROR(SEARCH("*tom*",O4:V4)))*SUBSTITUTE(O8:V8,".",":"))

Kevin
0

LVL 81

Expert Comment

ID: 39287318
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
0

LVL 80

Accepted Solution

byundt earned 500 total points
ID: 39287359
Do the numbers in O8:V8 include only two digits after the decimal point? If so, you could use:
=SUMIF(O4:V4,"*tom*",O8:V8)/14.4

If there might be 3 or more digits after the decimal point--and you want to round two digits--then consider:
=SUMPRODUCT(ISNUMBER(SEARCH("tom",O4:V4))*SUBSTITUTE(TEXT(O8:V8,"0.00"),".",":"))

Note that Kevin's suggestion will return an error value if one of the cells in O8:V8 is blank.
0

LVL 80

Expert Comment

ID: 39287368
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(SEARCH("tom",O4:V4))))*SUBSTITUTE(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.
0

Author Comment

ID: 39288189
byundt your formula =SUMPRODUCT(ISNUMBER(SEARCH("tom",O4:V4))*SUBSTITUTE(TEXT(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
0

LVL 80

Assisted Solution

byundt earned 500 total points
ID: 39288192
To add up the corresponding cells when O4:V4 does not include "tom", you might use:
=SUMPRODUCT(ISERR(SEARCH("tom",O4:V4))*SUBSTITUTE(TEXT(O8:V8,"0.00"),".",":"))
0

LVL 80

Expert Comment

ID: 39288197
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.

0

Author Comment

ID: 39288206
=SUMPRODUCT(ISNUMBER(SEARCH("tom",O4:V4))*SUBSTITUTE(TEXT(O8:V8,"0.00"),".",":"))

=SUMPRODUCT(ISERR(SEARCH("tom",O4:V4))*SUBSTITUTE(TEXT(O8:V8,"0.00"),".",":"))

These formulas did the trick the last one thanks a  million byundt
0

Author Closing Comment

ID: 39288208
the solutions that i got were remarkable....thanks to all  that contributed
0

## Featured Post

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference texâ€¦
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, tâ€¦
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.