Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct and wildcard
The data in my sheet is like:
Column A Column B 1 4602546wx 0 4615648dp 1 455586wd 1 465565ab and it is a huge data I want to count all the '1's where they have column B starting with 46. Any help on how can I ahieve this by using wildcard would be greatly appreciated. KD. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct and wildcard
=SUMPRODUCT(--(A1:A1000="1"),--(LEFT(B1:B1000,2)="46"))
If 1s are numbers (not TEXT) then: =SUMPRODUCT(--(A1:A1000=1),--(LEFT(B1:B1000,2)="46")) "kd" wrote: The data in my sheet is like: Column A Column B 1 4602546wx 0 4615648dp 1 455586wd 1 465565ab and it is a huge data I want to count all the '1's where they have column B starting with 46. Any help on how can I ahieve this by using wildcard would be greatly appreciated. KD. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct and wildcard
No need to test for 1 if it is just 1 and 0
=SUMPRODUCT(--(LEFT(B1:B1000,2)="46"),A1:A1000) a tad more efficient -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Toppers" wrote in message ... =SUMPRODUCT(--(A1:A1000="1"),--(LEFT(B1:B1000,2)="46")) If 1s are numbers (not TEXT) then: =SUMPRODUCT(--(A1:A1000=1),--(LEFT(B1:B1000,2)="46")) "kd" wrote: The data in my sheet is like: Column A Column B 1 4602546wx 0 4615648dp 1 455586wd 1 465565ab and it is a huge data I want to count all the '1's where they have column B starting with 46. Any help on how can I ahieve this by using wildcard would be greatly appreciated. KD. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct and wildcard
=SUMIF(B:B,"46*",A:A)
"kd" wrote: The data in my sheet is like: Column A Column B 1 4602546wx 0 4615648dp 1 455586wd 1 465565ab and it is a huge data I want to count all the '1's where they have column B starting with 46. Any help on how can I ahieve this by using wildcard would be greatly appreciated. KD. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sumproduct + wildcard | Excel Worksheet Functions | |||
SUMPRODUCT WITH WILDCARD | Excel Discussion (Misc queries) | |||
SumProduct w/WildCard | Excel Worksheet Functions | |||
wildcard in sumproduct? | Excel Worksheet Functions | |||
Sumproduct Wildcard | Excel Discussion (Misc queries) |