Hi, I have tried to figure out what the code above does as this is similar to a problem that I am facing. I am using Excel 2003 and I have two columns with data on resources. Column F is the role name and Column H contains people names.
What I would like to know is how to count the number of specific role names that have an associated people name. The trick here is that the role names are all different and but that contain a certain string of text in the cell which makes them similar, in this example it is "field engineer". So far I have tried,
=SUMPRODUCT(--(F3:F659="field engineer"), --(H3:H659<>""))
Which returned zero results, then I tried:
=SUMPRODUCT(--(F3:F659=("*" & "field engineer" & "*")),--(H3:H659<>""))
Which also returned zero results.
Any help would be appreciated.
Thanks
T. Valko wrote:
You're welcome. Thanks for the feedback!--BiffMicrosoft Excel MVP
23-Feb-10
You're welcome. Thanks for the feedback
-
Bif
Microsoft Excel MVP
Previous Posts In This Thread:
sumproduct - find text in string
Hi al
I tried to write a sumproduct formula to find various text within a rang
and then sum the totals for those rows found, but my attempts failed. I
anyone could suggest a formula - it would be very much appreciated
Here???s what I need the formula to do
The user enters strings of words into any cells within range(B37:H165
I need the formula to look in range(B37:H165) and find any rows containin
strings of text that include the words "production" or "installation" or "no
commission"
For those rows found, I need the formula to add all values in the same row
??? but over in column CB (the 1st months total column) i.e
Within range(B37:H165) the formula finds that the word ???production??? appear
within text entered into cell C4
???installation??? appears within text entered into cell D5
???production??? appears within text entered into cell B7
???non commission??? appears within text entered into cell H15
The formula then goes to range(CB37:CB165) and sums the values in in th
rows found ie. =CB40+CB51+CB70+CB15
FYI - Columns ???I??? to ???CA??? contain general data
FYI - Columns ???CB??? to ???CG??? contain monthly totals
-
Thank in advance for your hel
BeSmart
Just to be sure we understand.
Just to be sure we understand. If C40 contains "production" and D40 contain
"production" then should the formula be
=CB40+CB4
o
=CB4
-
Gary''s Student - gsnu20100
:
Difficult to test with data in B37:H165I entered data in A1
13Some cells have
Difficult to test with data in B37:H16
I entered data in A1
1
Some cells have text that include one of: cat, dog, hors
Cells G1:G13 have number
The formul
=SUMPRODUCT
(ISNUMBER(FIND("cat",A1
13))+ISNUMBER(FIND("dog",A1
13))+ISNUMBER(FIND("horse",A1
13)))*G1:G13
sum those G values that are in rows where a cell contains one of the word
Note, however, if (for example) A4 has CAT and D4 has DOG then G4 gets adde
twic
To avoid this, use helper column - I used K1:K1
In K1
=--(SUMPRODUCT(ISNUMBER(FIND("cat",A1
1))+ISNUMBER(FIND("dog",A1
1))+ISNUMBER(FIND("horse",A1
1)))>0
This is copied down the colum
To find required sum: =SUMPRODUCT(G1:G13,K1:K13
If required helper column (K) could be hidde
best wishe
-
Bernard Liengm
Microsoft Excel MV
people.stfx.ca/bliengm
email address: remove uppercase characters
Let's examine this simplified data set:...Ax.....oB.....C.....3...Xu.....T.....
Let's examine this simplified data set
....Ax.....oB.....C.....
....Xu.....T.....pA.....
....B.....O.....Cy.....1
....Ti.....iA.....Ai.....
You want to look for cells that contain A or B or C anywhere within the cel
and sum the corresponding value
Based on that sample data what result do you expect? Would the correc
result be
(3*3)+(1*5)+(2*10)+(2*8) = 5
Or:
3+5+10+8 = 26
--
Biff
Microsoft Excel MVP
HiThanks heaps for your assistance & questions.
Hi
Thanks heaps for your assistance & questions.
I need the row to be counted once if the word is found twice in the same row.
Although this is unlikely to happen as the other columns require information
not relating to my three words. However you never know with a user...
So to use Biff's example the calculation for his table would be:
.....Ax.....oB.....C.....3
.....Xu.....T.....pA.....5
.....B.....O.....Cy.....10
.....Ti.....iA.....Ai.....8
3+5+10+8=26
Perhaps the safest and simpliest way to do this (as Bernard suggested) would
be to have a hidden helper column? Or is there a way to avoid duplication
within the formula?
--
Thank for your help
BeSmart
:
Try this...Use cells to hold the criteria.
Try this...
Use cells to hold the criteria. Note: these cells *must* be in a horizontal
range.
A32 = production
B32 = installation
C32 = non commission
Then:
=SUMPRODUCT(--(MMULT(--(COUNTIF(OFFSET(B37:H165,ROW(B37:H165)-ROW(B37),,1),"*"&A32:C32&"*")>0),{1;1;1})>0),CB37:CB165)
--
Biff
Microsoft Excel MVP
Minor tweak that saves a few
Minor tweak that saves a few keystrokes:
=SUMPRODUCT(--(MMULT(COUNTIF(OFFSET(B37:H165,ROW(B37:H165)-ROW(B37),,1),"*"&A32:C32&"*"),{1;1;1})>0),CB37:CB165)
--
Biff
Microsoft Excel MVP
Thanks BiffIt looks complicated (I am not sure that I totally understand what
Thanks Biff
It looks complicated (I am not sure that I totally understand what it is doing
- but I get the general idea)
What really matters is that works brilliantly and helps me immensely!!!
Thank you very much for spending the time to help me!!
Cheers
BeSmart
You're welcome. Thanks for the feedback!--BiffMicrosoft Excel MVP
You're welcome. Thanks for the feedback!
--
Biff
Microsoft Excel MVP
Submitted via EggHeadCafe - Software Developer Portal of Choice
Store ASP.NET Site Visitor Stats in MongoDb
http://www.eggheadcafe.com/tutorial...ore-aspnet-site-visitor-stats-in-mongodb.aspx