| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMPRODUCT WITH WILDCARD
Can someone assist me with a formula to sumproduct with wildcards, please?
=SUMPRODUCT(--(F:F="NPT*"),--(A:A="PC*")) I've got this but it doesn't work!! -- Mark |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
SUMPRODUCT WITH WILDCARD
=sumproduct(--(left(f1:F100,3)="npt"),--(left(a1:a100,2)="pc"))
You can't use whole columns with this kind of formula. Mark wrote: Can someone assist me with a formula to sumproduct with wildcards, please? =SUMPRODUCT(--(F:F="NPT*"),--(A:A="PC*")) I've got this but it doesn't work!! -- Mark -- Dave Peterson |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
SUMPRODUCT WITH WILDCARD
hi! SUMIF can use wildcards, but only for one test, but SUMPRODUCT doesn't support wildcards directly. -via135 Mark Wrote: Can someone assist me with a formula to sumproduct with wildcards, please? =SUMPRODUCT(--(F:F="NPT*"),--(A:A="PC*")) I've got this but it doesn't work!! -- Mark -- via135 ------------------------------------------------------------------------ via135's Profile: http://www.excelforum.com/member.php...o&userid=26725 View this thread: http://www.excelforum.com/showthread...hreadid=521504 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
SUMPRODUCT WITH WILDCARD
Thanks Dave, but I'm getting #N/A and I can see there are some rows with this
info! Can you offer any suggestion? -- Mark "Dave Peterson" wrote: =sumproduct(--(left(f1:F100,3)="npt"),--(left(a1:a100,2)="pc")) You can't use whole columns with this kind of formula. Mark wrote: Can someone assist me with a formula to sumproduct with wildcards, please? =SUMPRODUCT(--(F:F="NPT*"),--(A:A="PC*")) I've got this but it doesn't work!! -- Mark -- Dave Peterson |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
SUMPRODUCT WITH WILDCARD
What formula did you use?
Do you have #n/a's in either of those two ranges? Mark wrote: Thanks Dave, but I'm getting #N/A and I can see there are some rows with this info! Can you offer any suggestion? -- Mark "Dave Peterson" wrote: =sumproduct(--(left(f1:F100,3)="npt"),--(left(a1:a100,2)="pc")) You can't use whole columns with this kind of formula. Mark wrote: Can someone assist me with a formula to sumproduct with wildcards, please? =SUMPRODUCT(--(F:F="NPT*"),--(A:A="PC*")) I've got this but it doesn't work!! -- Mark -- Dave Peterson -- Dave Peterson |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
SUMPRODUCT WITH WILDCARD
Dave,
Brilliant! It was the #N/A in column F! Sorted it, many thanks. -- Mark "Dave Peterson" wrote: What formula did you use? Do you have #n/a's in either of those two ranges? Mark wrote: Thanks Dave, but I'm getting #N/A and I can see there are some rows with this info! Can you offer any suggestion? -- Mark "Dave Peterson" wrote: =sumproduct(--(left(f1:F100,3)="npt"),--(left(a1:a100,2)="pc")) You can't use whole columns with this kind of formula. Mark wrote: Can someone assist me with a formula to sumproduct with wildcards, please? =SUMPRODUCT(--(F:F="NPT*"),--(A:A="PC*")) I've got this but it doesn't work!! -- Mark -- Dave Peterson -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| wildcard in sumproduct? | Excel Worksheet Functions | |||
| Using Wildcard characters in sumproduct | Excel Worksheet Functions | |||
| SUMPRODUCT with Wildcard | Excel Worksheet Functions | |||
| Sumproduct Wildcard | Excel Discussion (Misc queries) | |||
| Sumproduct with Wildcard * | Excel Worksheet Functions | |||
Linear Mode
