| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 . |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 . |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 . |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 . |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 . . |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Conditional Summing | Excel Worksheet Functions | |||
| conditional summing - help | Excel Worksheet Functions | |||
| Conditional Summing | Excel Worksheet Functions | |||
| Conditional summing | Excel Discussion (Misc queries) | |||
| "Conditional" summing | Excel Worksheet Functions | |||
Linear Mode
