Solved

sumproduct if

Posted on 2013-06-29
14
571 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
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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.

 
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 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 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 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 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: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
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.

820 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