VLookup and Count formula

[khammonds294]

I am trying to write a formula that adds total numbers from a column based on partial text matches that occur multiple times across many rows.

Main table contents:

(A) Campaign	(B) Date	(C) Description	(D) Area	(E) Click rates
Staff	October	Policy	HR	3
Student	December	Policy	HR	2
Staff	December	Event	Social	4
Staff	January	Training	Development	1
Student	January	Event	Social	7

I would like a formula that searches for specific words (such as Policy or Training in column (C) and then add up all of the Clicks (E) related to all rows containing that specific word, which would look like this:

Summary table:

(I) Theme	(J) Number of times promoted	(K) Total number of clicks
Policy	2	5
Event	2	11
Training	1	1

So ideally a formula to generate the totals showing in column (K) automatically!

Can anyone help? Thank you!

 

[Cubist]

Try:

=SUMIFS(E:E,C:C,I2)

 

[etaf]

doe sthis work
=COUNTIF($C$2:$C$6,I3)
=SUMIF($C$2:$C$6,I3,$E$2:$E$6)

Book4
	A	B	C	D	E	F	G	H	I	J	K	L
1	(A) Campaign	(B) Date	(C) Description	(D) Area	(E) Click rates				Summary table:
2	Staff	October	Policy	HR	3				(I) Theme	(J) Number of times promoted	(K) Total number of clicks
3	Student	December	Policy	HR	2				Policy	2	5
4	Staff	December	Event	Social	4				Event	2	11
5	Staff	January	Training	Development	1				Training	1	1
6	Student	January	Event	Social	7
Sheet1

Cell Formulas
Range	Formula
J3:J5	J3	=COUNTIF($C$2:$C$6,I3)
K3:K5	K3	=SUMIF($C$2:$C$6,I3,$E$2:$E$6)

 

[khammonds294]

hmm no these aren't working - I possibly over simplified my original query so I have attached a screen shot of my actual data. I think the tricky part is finding the common words across multiple rows and then adding all of the clicks associated with that word together!

The formula I am using in Column I is: =COUNTIF(C:C,"*DORA*") so now looking for a formula for Column J that will add all of the clicks associated with 'DORA' across all rows which is 7

 

[khammonds294]

Oh I have made it work by updating your formula and changing I3 to the partial text word "*DORA*" or appropriate other text!

THANK YOU!!!

 

[etaf]

you are welcome
I dont know why - but sometimes i find (questions on forums) that "*DORA*" does not work and changing to
"*"&"DORA"&"*"
can fix the issue, as i say i dont know why - but it happens quite frequently