|
sumproduct wildcats
Thanks very much for all the answers, I went through the posts and learnt a
lot, but I could find exactly what I need. The problem I've got is that one of my criterias is part of the cell. So, to be clear; I need all the cells which start with the word "pipe". pipe 100 w sp-pipe pipet 333 From these three I just need to get the first one. =SUMPRODUCT((Original!A2:A9101="024-02")*(Original!G2:G9101="400")*NOT(ISERROR((SEARCH ("pipe",Original!C2:C9101))))*(Original!H2:H910 1)) This is what I'm using at the moment. Thanks in advance, and again congratulations to all the people who colaborate with these answers. Martin |
Hi!
Try this: =SUMPRODUCT((Original!A2:A9101="024-02")*(Original!G2:G9101="400")*(LEFT(Original!C2:C 9101,4)="pipe")*(Original!H2:H9101)) Biff "Mosqui" wrote in message ... Thanks very much for all the answers, I went through the posts and learnt a lot, but I could find exactly what I need. The problem I've got is that one of my criterias is part of the cell. So, to be clear; I need all the cells which start with the word "pipe". pipe 100 w sp-pipe pipet 333 From these three I just need to get the first one. =SUMPRODUCT((Original!A2:A9101="024-02")*(Original!G2:G9101="400")*NOT(ISERROR((SEARCH ("pipe",Original!C2:C9101))))*(Original!H2:H910 1)) This is what I'm using at the moment. Thanks in advance, and again congratulations to all the people who colaborate with these answers. Martin |
does he not need this to eliminate the cell starting with with pipet?
=SUMPRODUCT((Original!A2:A9101="024-02")*(Original!G2:G9101="400")*(LEFT(Original!C2:C 9101,5)="pipe ")*(Original!H2:H9101)) "Biff" wrote: Hi! Try this: =SUMPRODUCT((Original!A2:A9101="024-02")*(Original!G2:G9101="400")*(LEFT(Original!C2:C 9101,4)="pipe")*(Original!H2:H9101)) Biff "Mosqui" wrote in message ... Thanks very much for all the answers, I went through the posts and learnt a lot, but I could find exactly what I need. The problem I've got is that one of my criterias is part of the cell. So, to be clear; I need all the cells which start with the word "pipe". pipe 100 w sp-pipe pipet 333 From these three I just need to get the first one. =SUMPRODUCT((Original!A2:A9101="024-02")*(Original!G2:G9101="400")*NOT(ISERROR((SEARCH ("pipe",Original!C2:C9101))))*(Original!H2:H910 1)) This is what I'm using at the moment. Thanks in advance, and again congratulations to all the people who colaborate with these answers. Martin |
Yeah, you're right!
But what if some cells only contain the the word pipe and don't have a trailing space as in: .........LEFT(Original!C2:C9101,5)="pipe ") Will work if: pipe fitter Won't work if: pipe Biff "duane" wrote in message ... does he not need this to eliminate the cell starting with with pipet? =SUMPRODUCT((Original!A2:A9101="024-02")*(Original!G2:G9101="400")*(LEFT(Original!C2:C 9101,5)="pipe ")*(Original!H2:H9101)) "Biff" wrote: Hi! Try this: =SUMPRODUCT((Original!A2:A9101="024-02")*(Original!G2:G9101="400")*(LEFT(Original!C2:C 9101,4)="pipe")*(Original!H2:H9101)) Biff "Mosqui" wrote in message ... Thanks very much for all the answers, I went through the posts and learnt a lot, but I could find exactly what I need. The problem I've got is that one of my criterias is part of the cell. So, to be clear; I need all the cells which start with the word "pipe". pipe 100 w sp-pipe pipet 333 From these three I just need to get the first one. =SUMPRODUCT((Original!A2:A9101="024-02")*(Original!G2:G9101="400")*NOT(ISERROR((SEARCH ("pipe",Original!C2:C9101))))*(Original!H2:H910 1)) This is what I'm using at the moment. Thanks in advance, and again congratulations to all the people who colaborate with these answers. Martin |
yup...maybe this?
=SUMPRODUCT((Original!A2:A9101="024-02")*(Original!G2:G9101="400")*(LEFT(Original!C2:C 9101,4)="pipe")*(LEN(Original!C2:C9101)=4)*(Origin al!H2:H9101)) "Biff" wrote: Yeah, you're right! But what if some cells only contain the the word pipe and don't have a trailing space as in: .........LEFT(Original!C2:C9101,5)="pipe ") Will work if: pipe fitter Won't work if: pipe Biff "duane" wrote in message ... does he not need this to eliminate the cell starting with with pipet? =SUMPRODUCT((Original!A2:A9101="024-02")*(Original!G2:G9101="400")*(LEFT(Original!C2:C 9101,5)="pipe ")*(Original!H2:H9101)) "Biff" wrote: Hi! Try this: =SUMPRODUCT((Original!A2:A9101="024-02")*(Original!G2:G9101="400")*(LEFT(Original!C2:C 9101,4)="pipe")*(Original!H2:H9101)) Biff "Mosqui" wrote in message ... Thanks very much for all the answers, I went through the posts and learnt a lot, but I could find exactly what I need. The problem I've got is that one of my criterias is part of the cell. So, to be clear; I need all the cells which start with the word "pipe". pipe 100 w sp-pipe pipet 333 From these three I just need to get the first one. =SUMPRODUCT((Original!A2:A9101="024-02")*(Original!G2:G9101="400")*NOT(ISERROR((SEARCH ("pipe",Original!C2:C9101))))*(Original!H2:H910 1)) This is what I'm using at the moment. Thanks in advance, and again congratulations to all the people who colaborate with these answers. Martin |
scratch that...brain cramp
"duane" wrote: yup...maybe this? =SUMPRODUCT((Original!A2:A9101="024-02")*(Original!G2:G9101="400")*(LEFT(Original!C2:C 9101,4)="pipe")*(LEN(Original!C2:C9101)=4)*(Origin al!H2:H9101)) "Biff" wrote: Yeah, you're right! But what if some cells only contain the the word pipe and don't have a trailing space as in: .........LEFT(Original!C2:C9101,5)="pipe ") Will work if: pipe fitter Won't work if: pipe Biff "duane" wrote in message ... does he not need this to eliminate the cell starting with with pipet? =SUMPRODUCT((Original!A2:A9101="024-02")*(Original!G2:G9101="400")*(LEFT(Original!C2:C 9101,5)="pipe ")*(Original!H2:H9101)) "Biff" wrote: Hi! Try this: =SUMPRODUCT((Original!A2:A9101="024-02")*(Original!G2:G9101="400")*(LEFT(Original!C2:C 9101,4)="pipe")*(Original!H2:H9101)) Biff "Mosqui" wrote in message ... Thanks very much for all the answers, I went through the posts and learnt a lot, but I could find exactly what I need. The problem I've got is that one of my criterias is part of the cell. So, to be clear; I need all the cells which start with the word "pipe". pipe 100 w sp-pipe pipet 333 From these three I just need to get the first one. =SUMPRODUCT((Original!A2:A9101="024-02")*(Original!G2:G9101="400")*NOT(ISERROR((SEARCH ("pipe",Original!C2:C9101))))*(Original!H2:H910 1)) This is what I'm using at the moment. Thanks in advance, and again congratulations to all the people who colaborate with these answers. Martin |
and the answer is both
=SUMPRODUCT(((LEFT(Original!C2:C9101,4)="pipe")*(L EN(Original!C2:C9101)=4)+(LEFT(Original!C2:C9101,5 )="pipe "))*(Original!A2:A9101="024-02")*(Original!G2:G9101="400")*(Original!H2:H9101) ) "duane" wrote: yup...maybe this? =SUMPRODUCT((Original!A2:A9101="024-02")*(Original!G2:G9101="400")*(LEFT(Original!C2:C 9101,4)="pipe")*(LEN(Original!C2:C9101)=4)*(Origin al!H2:H9101)) "Biff" wrote: Yeah, you're right! But what if some cells only contain the the word pipe and don't have a trailing space as in: .........LEFT(Original!C2:C9101,5)="pipe ") Will work if: pipe fitter Won't work if: pipe Biff "duane" wrote in message ... does he not need this to eliminate the cell starting with with pipet? =SUMPRODUCT((Original!A2:A9101="024-02")*(Original!G2:G9101="400")*(LEFT(Original!C2:C 9101,5)="pipe ")*(Original!H2:H9101)) "Biff" wrote: Hi! Try this: =SUMPRODUCT((Original!A2:A9101="024-02")*(Original!G2:G9101="400")*(LEFT(Original!C2:C 9101,4)="pipe")*(Original!H2:H9101)) Biff "Mosqui" wrote in message ... Thanks very much for all the answers, I went through the posts and learnt a lot, but I could find exactly what I need. The problem I've got is that one of my criterias is part of the cell. So, to be clear; I need all the cells which start with the word "pipe". pipe 100 w sp-pipe pipet 333 From these three I just need to get the first one. =SUMPRODUCT((Original!A2:A9101="024-02")*(Original!G2:G9101="400")*NOT(ISERROR((SEARCH ("pipe",Original!C2:C9101))))*(Original!H2:H910 1)) This is what I'm using at the moment. Thanks in advance, and again congratulations to all the people who colaborate with these answers. Martin |
That will only count cells with a len of 4 chars!
Now, it will count pipe and nothing else! Try this: =SUMPRODUCT((Original!A2:A9101="024-02")*(Original!G2:G9101=400)*(LEFT(Original!C2:C91 01&" ",5)="pipe ")*Original!H2:H9101) Also, if you ever need to calculate on just the specific word "pipe" you can pad the beginning of each cell with a space: ......LEFT(" "&Original!C2:C9101&" ",5)=" pipe ") OR ......LEFT(" "&Original!C2:C9101&" ",5)=" "&A1&" ") Variations of this method come in handy if you're doing "database like" searches. It's much more reliable then using: ISNUMBER(SEARCH(.....) but I still haven't found a method that is 100% bullet proof! Biff "duane" wrote in message ... yup...maybe this? =SUMPRODUCT((Original!A2:A9101="024-02")*(Original!G2:G9101="400")*(LEFT(Original!C2:C 9101,4)="pipe")*(LEN(Original!C2:C9101)=4)*(Origin al!H2:H9101)) "Biff" wrote: Yeah, you're right! But what if some cells only contain the the word pipe and don't have a trailing space as in: .........LEFT(Original!C2:C9101,5)="pipe ") Will work if: pipe fitter Won't work if: pipe Biff "duane" wrote in message ... does he not need this to eliminate the cell starting with with pipet? =SUMPRODUCT((Original!A2:A9101="024-02")*(Original!G2:G9101="400")*(LEFT(Original!C2:C 9101,5)="pipe ")*(Original!H2:H9101)) "Biff" wrote: Hi! Try this: =SUMPRODUCT((Original!A2:A9101="024-02")*(Original!G2:G9101="400")*(LEFT(Original!C2:C 9101,4)="pipe")*(Original!H2:H9101)) Biff "Mosqui" wrote in message ... Thanks very much for all the answers, I went through the posts and learnt a lot, but I could find exactly what I need. The problem I've got is that one of my criterias is part of the cell. So, to be clear; I need all the cells which start with the word "pipe". pipe 100 w sp-pipe pipet 333 From these three I just need to get the first one. =SUMPRODUCT((Original!A2:A9101="024-02")*(Original!G2:G9101="400")*NOT(ISERROR((SEARCH ("pipe",Original!C2:C9101))))*(Original!H2:H910 1)) This is what I'm using at the moment. Thanks in advance, and again congratulations to all the people who colaborate with these answers. Martin |
Thanks for your help, that was great !!!!
"Biff" wrote: Hi! Try this: =SUMPRODUCT((Original!A2:A9101="024-02")*(Original!G2:G9101="400")*(LEFT(Original!C2:C 9101,4)="pipe")*(Original!H2:H9101)) Biff "Mosqui" wrote in message ... Thanks very much for all the answers, I went through the posts and learnt a lot, but I could find exactly what I need. The problem I've got is that one of my criterias is part of the cell. So, to be clear; I need all the cells which start with the word "pipe". pipe 100 w sp-pipe pipet 333 From these three I just need to get the first one. =SUMPRODUCT((Original!A2:A9101="024-02")*(Original!G2:G9101="400")*NOT(ISERROR((SEARCH ("pipe",Original!C2:C9101))))*(Original!H2:H910 1)) This is what I'm using at the moment. Thanks in advance, and again congratulations to all the people who colaborate with these answers. Martin |
....(LEFT(Original!C2:C9101&" ",5)="pipe ")
Biff wrote: Yeah, you're right! But what if some cells only contain the the word pipe and don't have a trailing space as in: ........LEFT(Original!C2:C9101,5)="pipe ") Will work if: pipe fitter Won't work if: pipe Biff "duane" wrote in message ... does he not need this to eliminate the cell starting with with pipet? =SUMPRODUCT((Original!A2:A9101="024-02")*(Original!G2:G9101="400")*(LEFT(Original!C2:C 9101,5)="pipe ")*(Original!H2:H9101)) "Biff" wrote: Hi! Try this: =SUMPRODUCT((Original!A2:A9101="024-02")*(Original!G2:G9101="400")*(LEFT(Original!C2:C 9101,4)="pipe")*(Original!H2:H9101)) Biff "Mosqui" wrote in message ... Thanks very much for all the answers, I went through the posts and learnt a lot, but I could find exactly what I need. The problem I've got is that one of my criterias is part of the cell. So, to be clear; I need all the cells which start with the word "pipe". pipe 100 w sp-pipe pipet 333 From these three I just need to get the first one. =SUMPRODUCT((Original!A2:A9101="024-02")*(Original!G2:G9101="400")*NOT(ISERROR((SEARCH ("pipe",Original!C2:C9101))))*(Original!H2:H910 1)) This is what I'm using at the moment. Thanks in advance, and again congratulations to all the people who colaborate with these answers. Martin -- [1] The SumProduct function should implicitly coerce the truth values to their Excel numeric equivalents. [2] The lookup functions should have an optional argument for the return value, defaulting to #N/A in its absence. |
| All times are GMT +1. The time now is 06:35 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com