Solved

sumproduct if

Posted on 2013-06-29
14
586 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

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…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

739 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