Formula to Search multiple criteria of month/year within a cell and return total numbers from a different cell

[jqadri]

Hi All,

I've tried countif using substitute, but I know I'm missing something. I can't figure out how to incorporate counting column B (numbers) if SOME of the text from column A matches column C, provide the total numbers of column B. Ultimately, I would need 8 different text to find. I'm comparing data from September 2016-December 2016 to September 2017-December 2017.

Example:

Col A: week per month, I only care about the month/year - September 2016
September 2016 Week 1
September 2016 Week 2
October 2016 Week 1
October 2016 Week 2

Col B: total projects for that week completed
2
3
4
5

Col C: month/year
September 2016
October 2016
November 2016

So, if the text from col C matches some of the text in Col A, count all the corresponding numbers from Col B and provide the total.

 

[Marcelo Branco]

Maybe...


[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

A​

[/td][td="bgcolor: #DCE6F1"]

B​

[/td][td="bgcolor: #DCE6F1"]

C​

[/td][td="bgcolor: #DCE6F1"]

D​

[/td][td="bgcolor: #DCE6F1"]

E​

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

[/td][td]

Week​

[/td][td]

Projects​

[/td][td]

Keywords​

[/td][td][/td][td]

Result​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

2​

[/td][td]

September 2016 Week 1​

[/td][td]

2​

[/td][td]

September 2016​

[/td][td][/td][td]

14​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

3​

[/td][td]

September 2016 Week 2​

[/td][td]

3​

[/td][td]

October 2016​

[/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

4​

[/td][td]

October 2016 Week 1​

[/td][td]

4​

[/td][td]

November 2016​

[/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

5​

[/td][td]

October 2016 Week 2​

[/td][td]

5​

[/td][td][/td][td][/td][td][/td][/tr]
[/table]

Formula in E2
=SUMPRODUCT(SUMIF(A2:A5,C2:C4&"*",B2:B5))

Hope this helps

M.

 

[jqadri]

Thank you so much! That formula adds all of the projects, I need count of projects per month from all of the weeks within a month that are listed. So, september 2016 should show 5 projects and October 2016 should show 9 etc. I tried modifying that formula to count "C2" Specific Month(september 2016) to A:A, but the total ends up being "0". Does this formula only work if it's counting all of the cells within a column?

 

[Marcelo Branco]

Ok, this is simpler.
Change the formula in E2 to
=SUMIF(A$2:A$5,C2&"*",B$2:B$5)
and copy down

M.