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?

--
Thanx in advance,
Best Regards,

Faraz

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"))),"")

--
Jacob


"Faraz A. Qureshi" wrote:

> 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?
>
> --
> Thanx in advance,
> Best Regards,
>
> Faraz

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)

--
Biff
Microsoft Excel MVP


"Faraz A. Qureshi" <(E-Mail Removed)> wrote in
message news:846CD98F-0818-41A8-8E0F-(E-Mail Removed)...
>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?
>
> --
> Thanx in advance,
> Best Regards,
>
> Faraz

>=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>

--
Biff
Microsoft Excel MVP


"Jacob Skaria" <(E-Mail Removed)> wrote in message
news:63763041-E980-4795-82CC-(E-Mail Removed)...
> 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"))),"")
>
> --
> Jacob
>
>
> "Faraz A. Qureshi" wrote:
>
>> 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?
>>
>> --
>> Thanx in advance,
>> Best Regards,
>>
>> Faraz

Thanks brother!
Exceptional for sure!
--
Best Regards,

Faraz


"Jacob Skaria" wrote:

> 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"))),"")
>
> --
> Jacob
>
>
> "Faraz A. Qureshi" wrote:
>
> > 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?
> >
> > --
> > Thanx in advance,
> > Best Regards,
> >
> > Faraz

You are most welcome. Thanks for the feedback.

--
Jacob


"Faraz A. Qureshi" wrote:

> Thanks brother!
> Exceptional for sure!
> --
> Best Regards,
>
> Faraz
>
>
> "Jacob Skaria" wrote:
>
> > 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"))),"")
> >
> > --
> > Jacob
> >
> >
> > "Faraz A. Qureshi" wrote:
> >
> > > 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?
> > >
> > > --
> > > Thanx in advance,
> > > Best Regards,
> > >
> > > Faraz

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.

--
Jacob


"T. Valko" wrote:

> >=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>
>
> --
> Biff
> Microsoft Excel MVP
>
>
> "Jacob Skaria" <(E-Mail Removed)> wrote in message
> news:63763041-E980-4795-82CC-(E-Mail Removed)...
> > 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"))),"")
> >
> > --
> > Jacob
> >
> >
> > "Faraz A. Qureshi" wrote:
> >
> >> 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?
> >>
> >> --
> >> Thanx in advance,
> >> Best Regards,
> >>
> >> Faraz
>
>
> .
>

Thanx 4 the compliment buddy.
No doubt it is rather a nuclear bomb being kicked by an ant brutally!
:-)
--
Best Regards,

Faraz


"Jacob Skaria" wrote:

> 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.
>
> --
> Jacob
>
>
> "T. Valko" wrote:
>
> > >=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>
> >
> > --
> > Biff
> > Microsoft Excel MVP
> >
> >
> > "Jacob Skaria" <(E-Mail Removed)> wrote in message
> > news:63763041-E980-4795-82CC-(E-Mail Removed)...
> > > 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"))),"")
> > >
> > > --
> > > Jacob
> > >
> > >
> > > "Faraz A. Qureshi" wrote:
> > >
> > >> 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?
> > >>
> > >> --
> > >> Thanx in advance,
> > >> Best Regards,
> > >>
> > >> Faraz
> >
> >
> > .
> >

=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"))),"")

--
Best Regards,

Faraz


"T. Valko" wrote:

> 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)
>
> --
> Biff
> Microsoft Excel MVP
>
>
> "Faraz A. Qureshi" <(E-Mail Removed)> wrote in
> message news:846CD98F-0818-41A8-8E0F-(E-Mail Removed)...
> >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?
> >
> > --
> > Thanx in advance,
> > Best Regards,
> >
> > Faraz
>
>
> .
>

>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>

--
Biff
Microsoft Excel MVP


"Jacob Skaria" <(E-Mail Removed)> wrote in message
news:916EECBA-B2C3-4F8B-A207-(E-Mail Removed)...
> 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.
>
> --
> Jacob
>
>
> "T. Valko" wrote:
>
>> >=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>
>>
>> --
>> Biff
>> Microsoft Excel MVP
>>
>>
>> "Jacob Skaria" <(E-Mail Removed)> wrote in message
>> news:63763041-E980-4795-82CC-(E-Mail Removed)...
>> > 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"))),"")
>> >
>> > --
>> > Jacob
>> >
>> >
>> > "Faraz A. Qureshi" wrote:
>> >
>> >> 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?
>> >>
>> >> --
>> >> Thanx in advance,
>> >> Best Regards,
>> >>
>> >> Faraz
>>
>>
>> .
>>