SUMIFs on one tab taking data from 4 tabs

Hi all,

Can someone please assist me with a formula on my Calculations tab..

In cell B2, i`m trying to calculate the number of client projects which is to be taken from 4 'team tabs' (all identical) on my spreadsheet, but I am trying to figure out the number of client projects for each month/


For example on the calculations tab.cell B2 i have started this formula:

=(SUMIFS('Team Samuel'!B5:B39,"Client Project"&'Team Samuel'!2:2, "Jan"))+(SUMIFS('Team Rob'!B5:B39,"Client Project"&'Team Samuel'!2:2,"Jan"))

I am trying to say sumif where column B on team samuel + team rob + team team smeg etc has 'client project' in column B AND it has 'Jan' in row 2 on each of those tabs too.......... but I`m struggling. Any advice please?

Thank you!

Your formula makes no sense, I'm afraid. SUMIFS needs the sum range first (unlike SUMIF) and you can't concatenate an entire row to a bit of text.

My best guess, absent a workbook, would be something like:

=SUMIF('Team Samuel'!B5:B39,"Client Project",INDEX('Team Samuel'!5:39,0,MATCH("Jan",'Team Samuel'!2:2,0)))

although if "Client project" only appears once in column B you wouldn't really need SUMIF at all.

Hi Rory,

SOrry i had issues uploading my workbook which I have done now - maybe I need a countif? I am unsure. Client projecvt will appear multiple times in column B on different rows, but always the same column.

Thank you

It looks like you added a workbook while I was writing, so based on that, I'd use SUMPRODUCT:

=SUMPRODUCT(('Team Samuel'!$B$5:$B$39="Client Project")*(MONTH('Team Samuel'!$I$3:$NI$3)=1)*('Team Samuel'!$I$5:$NI$39))

and repeat for the other teams.

Hi Rory,

Thank you this seems to be brilliant/


Just to clarify.. as I anticipate more rows.. how can I amend the formula to widen the range:

=SUMPRODUCT(('Team Samuel'!$B$5:$B$39="Client Project")*(MONTH('Team Samuel'!$I$3:$NI$3)=1)*('Team Samuel'!$I$5:$NI$39))+SUMPRODUCT(('Team Rob'!$B$5:$B$39="Client Project")*(MONTH('Team Rob'!$I$3:$NI$3)=1)*('Team Rob'!$I$5:$NI$39))+SUMPRODUCT(('Team Smeg'!$B$5:$B$39="Client Project")*(MONTH('Team Smeg'!$I$3:$NI$3)=1)*('Team Smeg'!$I$5:$NI$39))+SUMPRODUCT(('Team Michael'!$B$5:$B$39="Client Project")*(MONTH('Team Michael'!$I$3:$NI$3)=1)*('Team Michael'!$I$5:$NI$39))

I might even want to go to 10000 just to be safe..

And also.. if I want to change the formula for each month to be counted, do I need to change the =1 to =2 (1 is for jan, 2 for feb) and so on?

Thank you

Try

=SUMPRODUCT(('Team Samuel'!$B$5:$B$1000="Client Project")*(MONTH('Team Samuel'!$I$3:$NI$3)=MONTH(A3&0))*('Team Samuel'!$I$5:$NI$1000))+SUMPRODUCT(('Team Rob'!$B$5:$B$1000="Client Project")*(MONTH('Team Rob'!$I$3:$NI$3)=MONTH(A3&0))*('Team Rob'!$I$5:$NI$1000))+SUMPRODUCT(('Team Smeg'!$B$5:$B$1000="Client Project")*(MONTH('Team Smeg'!$I$3:$NI$3)=MONTH(A3&0))*('Team Smeg'!$I$5:$NI$1000))+SUMPRODUCT(('Team Michael'!$B$5:$B$1000="Client Project")*(MONTH('Team Michael'!$I$3:$NI$3)=MONTH(A3&0))*('Team Michael'!$I$5:$NI$1000))


and change 39 to 1000 or 10000

Great thanks John, think this works! :D