Conditional Summing

[Faraz A. Qureshi]

I have 3 ranges of different sizes on different sheets as follows:

Range 1 Sheet 1
Name Amount
Name 18 1,496
Name 11 9,119
Name 13 8,626
Name 17 5,662
Name 19 1,624
Name 10 8,512
Name 16 8,355
Name 14 1,155

Range 2 Sheet 2
Name Amount
Name 19 3,730
Name 11 6,965
Name 14 4,102
Name 12 9,781
Name 10 7,632
Name 20 4,070

Range 3 Sheet 3
Name Amount
Name 17 7,760
Name 19 9,221
Name 12 3,504
Name 15 8,158
Name 11 7,560
Name 16 2,590
Name 13 7,971
Name 14 4,920
Name 20 1,047

What formula would sum up the respective names of ONLY those names which are
appearing ALL the three ranges?, i.e. sums of

Name 18 1 0
Name 11 3 23,644
Name 13 2 0
Name 17 2 0
Name 19 3 14,575
Name 10 2 0
Name 16 2 0
Name 14 3 10,177
Name 12 2 0
Name 20 2 0
Name 15 1 0


Any suggestions?

 

[Jacob Skaria]

Hi Faraz

To COUNTIF multiple sheets. Sheet1, Sheet2, Sheet3
With A2 = "Name 17" try the below formula in cell B2

=SUMPRODUCT(COUNTIF(INDIRECT("'"&"sheet"&ROW(INDIRECT("1:3"))&"'!A:A"),A2))

'To SUMIF multiple sheets Sheet1, Sheet2 and Sheet3
With A2 = "Name 17" try the below formula in cell B2

=SUMPRODUCT(SUMIF(INDIRECT("'"&"sheet"&ROW(INDIRECT("1:3"))&"'!A:A"),A2,INDIRECT("'"&"sheet"&ROW(INDIRECT("1:3"))&"'!b:b")))

Combining the above two formulas with IF()

=IF(SUMPRODUCT(COUNTIF(INDIRECT("'"&"sheet"&ROW(INDIRECT("1:3"))&"'!A:A"),A2))=3,SUMPRODUCT(SUMIF(INDIRECT("'"&"sheet"&ROW(INDIRECT("1:3"))&"'!A:A"),A2,INDIRECT("'"&"sheet"&ROW(INDIRECT("1:3"))&"'!b:b"))),"")

 

[T. Valko]

No elegant way to do this. "Brute force" seems to be the best way to go.

Something like this...

=IF(COUNTIF(...)+COUNTIF(...)+COUNTIF(...)=3,SUMIF(...)+SUMIF(...)+SUMIF(...),0)

Or, if you already have the counts for the names:

=IF(B2=3,SUMIF(...)+SUMIF(...)+SUMIF(...),0)

 

[T. Valko]

=IF(SUMPRODUCT(COUNTIF(INDIRECT("'"&"sheet"&ROW(INDIRECT("1:3"))&"'!A:A"),A2))=3,SUMPRODUCT(SUMIF(INDIRECT("'"&"sheet"&ROW(INDIRECT("1:3"))&"'!A:A"),A2,INDIRECT("'"&"sheet"&ROW(INDIRECT("1:3"))&"'!b:b"))),"")

That's like trying to kill an ant with an atomic bomb! <g>

 

[Faraz A. Qureshi]

Thanks brother!
Exceptional for sure!

 

[Jacob Skaria]

You are most welcome. Thanks for the feedback.

 

[Jacob Skaria]

Biff, I accept that. Faraz is quite proficient in MSExcel and I am sure he is
not looking for a formula to work with just 3 sheets.

 

[Faraz A. Qureshi]

Thanx 4 the compliment buddy.
No doubt it is rather a nuclear bomb being kicked by an ant brutally!

 

[Faraz A. Qureshi]

=IF(SUMPRODUCT(COUNTIF(INDIRECT("'"&"sheet"&ROW(INDIRECT("1:3"))&"'!A:A"),A2))=3,SUMPRODUCT(SUMIF(INDIRECT("'"&"sheet"&ROW(INDIRECT("1:3"))&"'!A:A"),A2,INDIRECT("'"&"sheet"&ROW(INDIRECT("1:3"))&"'!b:b"))),"")

 

[T. Valko]

I am sure he is not looking for a formula to

work with just 3 sheets.

Hmmm....

Then why did his post explicitly say he had 3 sheets?

I have 3 ranges of different sizes on different sheets
Range 1 Sheet 1
Range 2 Sheet 2
Range 3 Sheet 3

So, the next time he posts a question where he says he has 3 sheets should I
automatically assume that he really has 25 sheets or 100 sheets?

Maybe the next time I'll just skip that post! <VBG>

 

[Jacob Skaria]

So, the next time he posts a question where he says he has 3 sheets should I

automatically assume that he really has 25 sheets or 100 sheets?

Biff, it all depends on the skill level of the OP. Faraz regularly post
queries in 'Excel Programming' and so I can understand his skill level.