ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)
-   -   Conditional Summing (https://www.excelbanter.com/excel-discussion-misc-queries/252541-conditional-summing.html)

Faraz A. Qureshi
Conditional Summing
 
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

Jacob Skaria
Conditional Summing
 
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(INDIR ECT("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(INDIREC T("1:3"))&"'!A:A"),A2,INDIRECT("'"&"sheet"&ROW(IND IRECT("1:3"))&"'!b:b")))

Combining the above two formulas with IF()

=IF(SUMPRODUCT(COUNTIF(INDIRECT("'"&"sheet"&ROW(IN DIRECT("1:3"))&"'!A:A"),A2))=3,SUMPRODUCT(SUMIF(IN DIRECT("'"&"sheet"&ROW(INDIRECT("1:3"))&"'!A:A"),A 2,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

T. Valko
Conditional Summing
 
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" wrote in
message ...
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



T. Valko
Conditional Summing
 
=IF(SUMPRODUCT(COUNTIF(INDIRECT("'"&"sheet"&ROW(I NDIRECT("1:3"))&"'!A:A"),A2))=3,SUMPRODUCT(SUMIF(I NDIRECT("'"&"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" wrote in message
...
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(INDIR ECT("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(INDIREC T("1:3"))&"'!A:A"),A2,INDIRECT("'"&"sheet"&ROW(IND IRECT("1:3"))&"'!b:b")))

Combining the above two formulas with IF()

=IF(SUMPRODUCT(COUNTIF(INDIRECT("'"&"sheet"&ROW(IN DIRECT("1:3"))&"'!A:A"),A2))=3,SUMPRODUCT(SUMIF(IN DIRECT("'"&"sheet"&ROW(INDIRECT("1:3"))&"'!A:A"),A 2,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



Faraz A. Qureshi
Conditional Summing
 
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(INDIR ECT("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(INDIREC T("1:3"))&"'!A:A"),A2,INDIRECT("'"&"sheet"&ROW(IND IRECT("1:3"))&"'!b:b")))

Combining the above two formulas with IF()

=IF(SUMPRODUCT(COUNTIF(INDIRECT("'"&"sheet"&ROW(IN DIRECT("1:3"))&"'!A:A"),A2))=3,SUMPRODUCT(SUMIF(IN DIRECT("'"&"sheet"&ROW(INDIRECT("1:3"))&"'!A:A"),A 2,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

Jacob Skaria
Conditional Summing
 
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(INDIR ECT("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(INDIREC T("1:3"))&"'!A:A"),A2,INDIRECT("'"&"sheet"&ROW(IND IRECT("1:3"))&"'!b:b")))

Combining the above two formulas with IF()

=IF(SUMPRODUCT(COUNTIF(INDIRECT("'"&"sheet"&ROW(IN DIRECT("1:3"))&"'!A:A"),A2))=3,SUMPRODUCT(SUMIF(IN DIRECT("'"&"sheet"&ROW(INDIRECT("1:3"))&"'!A:A"),A 2,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

Jacob Skaria
Conditional Summing
 
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(I NDIRECT("1:3"))&"'!A:A"),A2))=3,SUMPRODUCT(SUMIF(I NDIRECT("'"&"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" wrote in message
...
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(INDIR ECT("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(INDIREC T("1:3"))&"'!A:A"),A2,INDIRECT("'"&"sheet"&ROW(IND IRECT("1:3"))&"'!b:b")))

Combining the above two formulas with IF()

=IF(SUMPRODUCT(COUNTIF(INDIRECT("'"&"sheet"&ROW(IN DIRECT("1:3"))&"'!A:A"),A2))=3,SUMPRODUCT(SUMIF(IN DIRECT("'"&"sheet"&ROW(INDIRECT("1:3"))&"'!A:A"),A 2,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


.

Faraz A. Qureshi
Conditional Summing
 
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(I NDIRECT("1:3"))&"'!A:A"),A2))=3,SUMPRODUCT(SUMIF(I NDIRECT("'"&"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" wrote in message
...
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(INDIR ECT("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(INDIREC T("1:3"))&"'!A:A"),A2,INDIRECT("'"&"sheet"&ROW(IND IRECT("1:3"))&"'!b:b")))

Combining the above two formulas with IF()

=IF(SUMPRODUCT(COUNTIF(INDIRECT("'"&"sheet"&ROW(IN DIRECT("1:3"))&"'!A:A"),A2))=3,SUMPRODUCT(SUMIF(IN DIRECT("'"&"sheet"&ROW(INDIRECT("1:3"))&"'!A:A"),A 2,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


.

Faraz A. Qureshi
Conditional Summing
 
=IF(SUMPRODUCT(COUNTIF(INDIRECT("'"&"sheet"&ROW(IN DIRECT("1:3"))&"'!A:A"),A2))=3,SUMPRODUCT(SUMIF(IN DIRECT("'"&"sheet"&ROW(INDIRECT("1:3"))&"'!A:A"),A 2,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" wrote in
message ...
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


.

T. Valko
Conditional Summing
 
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" wrote in message
...
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(I NDIRECT("1:3"))&"'!A:A"),A2))=3,SUMPRODUCT(SUMIF(I NDIRECT("'"&"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" wrote in message
...
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(INDIR ECT("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(INDIREC T("1:3"))&"'!A:A"),A2,INDIRECT("'"&"sheet"&ROW(IND IRECT("1:3"))&"'!b:b")))

Combining the above two formulas with IF()

=IF(SUMPRODUCT(COUNTIF(INDIRECT("'"&"sheet"&ROW(IN DIRECT("1:3"))&"'!A:A"),A2))=3,SUMPRODUCT(SUMIF(IN DIRECT("'"&"sheet"&ROW(INDIRECT("1:3"))&"'!A:A"),A 2,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


.

Jacob Skaria
Conditional Summing
 
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.

--
Jacob


"T. Valko" wrote:

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" wrote in message
...
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(I NDIRECT("1:3"))&"'!A:A"),A2))=3,SUMPRODUCT(SUMIF(I NDIRECT("'"&"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" wrote in message
...
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(INDIR ECT("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(INDIREC T("1:3"))&"'!A:A"),A2,INDIRECT("'"&"sheet"&ROW(IND IRECT("1:3"))&"'!b:b")))

Combining the above two formulas with IF()

=IF(SUMPRODUCT(COUNTIF(INDIRECT("'"&"sheet"&ROW(IN DIRECT("1:3"))&"'!A:A"),A2))=3,SUMPRODUCT(SUMIF(IN DIRECT("'"&"sheet"&ROW(INDIRECT("1:3"))&"'!A:A"),A 2,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


.



.


All times are GMT +1. The time now is 09:37 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com