Home |
Search |
Today's Posts |
#1
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
I often use the SUMPRODUCT formula in the following manner:
=SUMPRODUCT(--(OFFICE=$B12),--(REVENUE<""),--(REGION="1 - Canada")) where... OFFICE, REVENUE and REGION are all 'Names' given to column ranges I often, however, find that my data is structured in such a way that I could really benefit from being able to use other formulas in conjunction with the SUMPRODUCT format. For instance, it would be great if I could structure the 'REGION' section of the above formula to only have to look at the first character (I'll attempt to demonstrate my thinking by way of the following altered version of the above formula): =SUMPRODUCT(--(OFFICE=$B12),--(REVENUE<""),--((LEFT(REGION,1))=1)) Is something like this possible? Thanks in advance! |
#2
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
hi cdavidson
you can test it by tollsformula auditingEvaluate formula... the way it looks,,,perhars you can do it something like this =SUMPRODUCT(--(OFFICE=$B12),--(REVENUE<""),--((LEFT(REGION,1))="1")) perhaps enclosing 1 with double quotes. "1" to be read as text by LEFT function... regards -- ***** birds of the same feather flock together.. "cdavidson" wrote: I often use the SUMPRODUCT formula in the following manner: =SUMPRODUCT(--(OFFICE=$B12),--(REVENUE<""),--(REGION="1 - Canada")) where... OFFICE, REVENUE and REGION are all 'Names' given to column ranges I often, however, find that my data is structured in such a way that I could really benefit from being able to use other formulas in conjunction with the SUMPRODUCT format. For instance, it would be great if I could structure the 'REGION' section of the above formula to only have to look at the first character (I'll attempt to demonstrate my thinking by way of the following altered version of the above formula): =SUMPRODUCT(--(OFFICE=$B12),--(REVENUE<""),--((LEFT(REGION,1))=1)) Is something like this possible? Thanks in advance! |
#3
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
That formula will work, you just need to keep in mind that the LEFT function
returns a text value. Thus, you should be checking if it is equal to "1" instead of 1. --(LEFT(REGION,1)="1") HTH, Elkar "cdavidson" wrote: I often use the SUMPRODUCT formula in the following manner: =SUMPRODUCT(--(OFFICE=$B12),--(REVENUE<""),--(REGION="1 - Canada")) where... OFFICE, REVENUE and REGION are all 'Names' given to column ranges I often, however, find that my data is structured in such a way that I could really benefit from being able to use other formulas in conjunction with the SUMPRODUCT format. For instance, it would be great if I could structure the 'REGION' section of the above formula to only have to look at the first character (I'll attempt to demonstrate my thinking by way of the following altered version of the above formula): =SUMPRODUCT(--(OFFICE=$B12),--(REVENUE<""),--((LEFT(REGION,1))=1)) Is something like this possible? Thanks in advance! |
#4
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
Have you tried it? The best way to learn is by trying. In essence, it should
work, but there are some gotchas to be wary of it. Try it and see if you get any. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "cdavidson" wrote in message ... I often use the SUMPRODUCT formula in the following manner: =SUMPRODUCT(--(OFFICE=$B12),--(REVENUE<""),--(REGION="1 - Canada")) where... OFFICE, REVENUE and REGION are all 'Names' given to column ranges I often, however, find that my data is structured in such a way that I could really benefit from being able to use other formulas in conjunction with the SUMPRODUCT format. For instance, it would be great if I could structure the 'REGION' section of the above formula to only have to look at the first character (I'll attempt to demonstrate my thinking by way of the following altered version of the above formula): =SUMPRODUCT(--(OFFICE=$B12),--(REVENUE<""),--((LEFT(REGION,1))=1)) Is something like this possible? Thanks in advance! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|