0

On the main sheet I have a list of names where I have successfully counted the number of times they appear on another twenty 'project' sheets in the cell range C10:C100.

On Cell B5 on every project sheet there is a figure. I want to return the sum of the figures based on how many times a particular name appears from the list on each project sheet.

For example, if john is found to appear in range C10:C100 on the first and last project sheets, where the figure for the first is 10,000 and for the last 5,000, the formula should return value of 15,000.

The formula I am using is: 

=SUMIF(project1!$C$10:$C$100,MAIN!B4,project1!$B$5)+SUMIF('project2'!$C$10:$C$100,MAIN!B4,'project2'!$B$5:$D$5)+SUMIF('project3'!$C$10:$C$100,MAIN!B4,'project3'!$B$5:$D$5)+SUMIF('project4'!$C$10:$C$100,MAIN!B4,'project4'!$B$5:$D$5)....and so on.

It works well if the name is at the top of the range but if it is further down the list in that range then it returns 0.

What does it take to fix this?

pnuts
  • 58,317
  • 11
  • 87
  • 139
Rg786
  • 305
  • 3
  • 8
  • 26
  • 1
    if you want to avoid a long formula and sum it up in a bit of a different way, see [this post](http://stackoverflow.com/questions/34135482/how-to-change-this-excel-formula/34136039#34136039). It basically lists the worksheet names and then uses `INDIRECT` to write the formulas. Then adds it all up at the end. – Scott Holtzman Dec 09 '15 at 19:21
  • 1
    yes @pnuts ... it may, but I still thought it worth exposing the user too, in case it proves helpful. But your reminder is appreciated because it's very worth noting as well :) – Scott Holtzman Dec 09 '15 at 19:59

2 Answers2

3

Please try: 

=COUNTIF(Project1!C10:C100,"John")*Project1!B5+

# 

+COUNTIF(Project20!C10:C100,"John")*Project20!B5

with replacing # to suit for the other 18 sheets.

pnuts
  • 58,317
  • 11
  • 87
  • 139
  • Yes I missed the value is one cell per sheet. I like yours but it assumes John is on the list only once. That may be true, but you could use `IF(COUNTIF(Project1!C10:C100,"John"),Project1!B5,0) + ... +IF(COUNTIF(Project20!C10:C100,"John"),Project20!B5,0)` and it would not matter if it was once or thirty. – Scott Craner Dec 09 '15 at 19:20
2

I think this array formula might work for you

=SUM(IF(COUNTIF(INDIRECT("project"&{1,2,3,4}&"!c10:C100"),"="&MAIN!$B$4),
SUMIF(INDIRECT("project"&{1,2,3,4}&"!B5:D5"),">0")))

Change the {1,2,3,4} to an array constant of all your sheet numbers

Bob Phillips
  • 437
  • 1
  • 3
  • 7
  • 1
    That is the value he wants to check is in the range C10:C100 on each of the project sheets. My reading was that if the value in Main!B4 was found ANYWHERE in C10:C100 add the values in B5:D5, otherwise ignore them. – Bob Phillips Dec 09 '15 at 19:30
  • 1
    That's what comes when we have to tease the question out from the formula tried so far :-). As for D5, he missed that for project1 (at least I hope that was a miss). – Bob Phillips Dec 09 '15 at 19:36
  • 1
    @BobPhillips my apologies of the question is confusing, i tried to simplify the question, in doing so i may have confused things a little. the project are not named project1 to project20, but instead they have different names entirely. this explains the difficulty in applying this formula as i am struggling for substituting the projects names into the array. eg, project names are iSMART, Seedcorn 1, Leeds Phd...and so on to around 20 of them. – Rg786 Dec 09 '15 at 20:17
  • No need to apologise mate, I was just having some friendly with pnuts. If the names are not structured, put them in a list in say Main!M1:M20 (or exactly however many there are), and use =SUM(IF(COUNTIF(INDIRECT("'"&MAIN!$M$1:$M$20&"'!C10:C100"),"="&MAIN!$B$4), SUMIF(INDIRECT("'"&MAIN!$M$1:$M$20&"'!B5:D5"),">0"))) – Bob Phillips Dec 09 '15 at 23:34