Improve company productivity with a Business Account.Sign Up

x
?
Solved

sumproduct if

Posted on 2013-06-29
14
Medium Priority
?
641 Views
Last Modified: 2013-06-30
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
Comment
Question by:Svgmassive
  • 6
  • 4
  • 4
14 Comments
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 39287287
Please describe the function in English without the formulas.

Kevin
0
 

Author Comment

by:Svgmassive
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

by:Svgmassive
ID: 39287297
the substitute convert the entries to a time format
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
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

by:Svgmassive
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

by:zorvek (Kevin Jones)
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

by:zorvek (Kevin Jones)
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 81

Accepted Solution

by:
byundt earned 2000 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 81

Expert Comment

by:byundt
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

by:Svgmassive
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 81

Assisted Solution

by:byundt
byundt earned 2000 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 81

Expert Comment

by:byundt
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.

Brad
0
 

Author Comment

by:Svgmassive
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

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

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Microsoft's Excel has many features that most people will never need nor take advantage of.  Conditional formatting is one feature that you may find a necessity once you start using it.
This article describes a serious pitfall that can happen when deleting shapes using VBA.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

601 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question