?
Solved

sumproduct if

Posted on 2013-06-29
14
Medium Priority
?
605 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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

Independent Software Vendors: 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

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

762 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