Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with SUMPRODUCT please : )
Here is what I'm dealing with:
Summary sheet: A B C 1 222220 3330 (Total of data from column L on other worksheet) 3333 33353 2 222220 3330 3330 33353 3 222229 3330 3333 33353 Dump sheet(different worksheet, same workbook) Cannot have colomn headers/titles A B C ............ L 1 222220 ..... 3333 ..... 596.35 2 222220 ..... 3330 ..... 254.98 3 222220 ..... 3330 ..... 1547.36 4 222229 ..... 3330 ..... 259.36 5 222229 ..... 33353 ..... 12.36 I need to the sum of the data in column L if the row contains a particular number in column A and a particular number in column C. For example, I need the total of expenses in account 222220 AND within department 3330 (so that would be 254.98 and 1547.36). The data goes on for a few thousand lines, so a formula to add the data automatically would be great! Thanks to the help I've already received on this site, I have tried: =SUMPRODUCT(--('FAST Dump'!$A$1:$A$2000=B6), --('FAST Dump'!$C$1:$C$2000=C10), 'FAST Dump'!$L$1:$L$2000) Where 'FAST Dump' is the data sheet, B6 is my first criteria and C10 is my second criteria. All I get is '0'. The formula evaluation apparently works out the fist part okay (--('FAST Dump'!$A$1:$A$2000=B6), but the problem comes with the second part --('FAST Dump'!$C$1:$C$2000=C10), where all results are returned FALSE and there are definately multiple 'C10''s in the data sheet area selected. It does this whether I hit 'ctrl, shift, enter' for the array or not, including the dollar signs or not, selecting the data range or typing it in, and selecting the cell with the criteria (B7 and C10) or simply typing in the criteria. I'm up to 12 hours now spent on trying to work this out, and it's part of a time-sensitive overhaul of our budget system. Any suggestions would be greatly appreciated! Thanks, Sierra :) |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with SUMPRODUCT please : )
Bit confusing. You summary sheet seems have the criteria in A and B but you
talk about B6 and C10. Let's get that sorted first. Also it is going to be easier on the summary sheet to repeat the 222220 and not have blank cells. We can hid duplicates later with conditional formatting Please come back with clarification - we DO want to help -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email "Sierra" wrote in message ... Here is what I'm dealing with: Summary sheet: A B C 1 222220 3330 (Total of data from column L on other worksheet) 3333 33353 2 222220 3330 3330 33353 3 222229 3330 3333 33353 Dump sheet(different worksheet, same workbook) Cannot have colomn headers/titles A B C ............ L 1 222220 ..... 3333 ..... 596.35 2 222220 ..... 3330 ..... 254.98 3 222220 ..... 3330 ..... 1547.36 4 222229 ..... 3330 ..... 259.36 5 222229 ..... 33353 ..... 12.36 I need to the sum of the data in column L if the row contains a particular number in column A and a particular number in column C. For example, I need the total of expenses in account 222220 AND within department 3330 (so that would be 254.98 and 1547.36). The data goes on for a few thousand lines, so a formula to add the data automatically would be great! Thanks to the help I've already received on this site, I have tried: =SUMPRODUCT(--('FAST Dump'!$A$1:$A$2000=B6), --('FAST Dump'!$C$1:$C$2000=C10), 'FAST Dump'!$L$1:$L$2000) Where 'FAST Dump' is the data sheet, B6 is my first criteria and C10 is my second criteria. All I get is '0'. The formula evaluation apparently works out the fist part okay (--('FAST Dump'!$A$1:$A$2000=B6), but the problem comes with the second part --('FAST Dump'!$C$1:$C$2000=C10), where all results are returned FALSE and there are definately multiple 'C10''s in the data sheet area selected. It does this whether I hit 'ctrl, shift, enter' for the array or not, including the dollar signs or not, selecting the data range or typing it in, and selecting the cell with the criteria (B7 and C10) or simply typing in the criteria. I'm up to 12 hours now spent on trying to work this out, and it's part of a time-sensitive overhaul of our budget system. Any suggestions would be greatly appreciated! Thanks, Sierra :) |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with SUMPRODUCT please : )
Thanks,
Sorry, I was just setting the sheets up as an example, my actual summery sheet looks like this: A B C D 6 611026 P/T Salaries 60000.00 7 3961 ? 8 3962 ? 9 3963 ? 10 3964 ? 11 3965 ? 12 3966 ? 13 3967 14 3968 15 39610 16 39611 17 36912 18 39613 19 39614 20 39615 21 39616 22 611028 Sessionals 38000.00 23 3961 ? 24 3962 ? 25 3963 ? 26 3964 27 3965 28 3966 29 3967 30 3967 31 3968 32 36912 33 39610 34 39611 35 39613 36 39614 37 39615 38 39616 etc., etc. Column B holds the account numbers and column C holds the department numbers. My example of B6 and C10 for criteria was trying to find the total of expenses in account 611026 and within 3964 I can certainly enter the account numbers in each line, it just looked cluttered to me. Thanks again and I look forward to your response! Sierra :) "Bernard Liengme" wrote: Bit confusing. You summary sheet seems have the criteria in A and B but you talk about B6 and C10. Let's get that sorted first. Also it is going to be easier on the summary sheet to repeat the 222220 and not have blank cells. We can hid duplicates later with conditional formatting Please come back with clarification - we DO want to help -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email "Sierra" wrote in message ... Here is what I'm dealing with: Summary sheet: A B C 1 222220 3330 (Total of data from column L on other worksheet) 3333 33353 2 222220 3330 3330 33353 3 222229 3330 3333 33353 Dump sheet(different worksheet, same workbook) Cannot have colomn headers/titles A B C ............ L 1 222220 ..... 3333 ..... 596.35 2 222220 ..... 3330 ..... 254.98 3 222220 ..... 3330 ..... 1547.36 4 222229 ..... 3330 ..... 259.36 5 222229 ..... 33353 ..... 12.36 I need to the sum of the data in column L if the row contains a particular number in column A and a particular number in column C. For example, I need the total of expenses in account 222220 AND within department 3330 (so that would be 254.98 and 1547.36). The data goes on for a few thousand lines, so a formula to add the data automatically would be great! Thanks to the help I've already received on this site, I have tried: =SUMPRODUCT(--('FAST Dump'!$A$1:$A$2000=B6), --('FAST Dump'!$C$1:$C$2000=C10), 'FAST Dump'!$L$1:$L$2000) Where 'FAST Dump' is the data sheet, B6 is my first criteria and C10 is my second criteria. All I get is '0'. The formula evaluation apparently works out the fist part okay (--('FAST Dump'!$A$1:$A$2000=B6), but the problem comes with the second part --('FAST Dump'!$C$1:$C$2000=C10), where all results are returned FALSE and there are definately multiple 'C10''s in the data sheet area selected. It does this whether I hit 'ctrl, shift, enter' for the array or not, including the dollar signs or not, selecting the data range or typing it in, and selecting the cell with the criteria (B7 and C10) or simply typing in the criteria. I'm up to 12 hours now spent on trying to work this out, and it's part of a time-sensitive overhaul of our budget system. Any suggestions would be greatly appreciated! Thanks, Sierra :) |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with SUMPRODUCT please : )
I will assume the Data sheet (with name Data) looks like:
A B C ............ L 1 222220 ..... 3333 ..... 596.35 2 222220 ..... 3330 ..... 254.98 3 222220 ..... 3330 ..... 1547.36 4 222229 ..... 3330 ..... 259.36 5 222229 ..... 33353 ..... 12.36 That the summary sheet layout is A B C 1 222220 3330 (Total of data from column L on other worksheet) 2 222220 3333 3 222220 33353 2 222221 3330 4 222221 3330 5 222221 33353 6 222229 3330 7 222229 3333 8 222229 33353 In C1 to sum all the L-values for the corresponding A and B value =SUMPRODUCT(--(Data!$A$1:$A$1000=A1),--(Data!$B$1:$B$1000=C1), $L$1:$L$1000) This can be copied down the column to complete the table. Adjust the ranges as needed. Beware that SUMPRODUCT cannot use full-column references such as A:A. I duplicated the data in A to make it easier to write and copy the formula. Bt I agree it looks tidier with out. So we will use Conditional formatting. Select all the A after A1 (so A2:A1000 or whatever) column entries Use Format Condition Formatting Set the dialog to Formula is: =A2=A1 and then set the font colour to white (or whatever you cell background is). So now when you have in A2 the same values as in A1 (or if A3 the same as in A2,,,,) the entry will be hidden For more on SUMPRODUCT see http://www.xldynamic.com/source/xld.SUMPRODUCT.html http://mcgimpsey.com/excel/formulae/doubleneg.html best wishes -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with SUMPRODUCT please : )
Thanks for your patience with my lack of clarity! The formula you have proposed is the same one I've been trying to use, but the total keeps coming out as zero. When I evaluate the calculations, the first part (--(Data!$A$1:$A$1000=A1) seems to be working fine, but the second part (Data!$B$1:$B$1000=C1) comes up as FALSE for all rows, therefore '0', therefore my total on the summary sheet is '0', this is where I'm at a loss. Thanks, Sierra :) "Bernard Liengme" wrote: I will assume the Data sheet (with name Data) looks like: A B C ............ L 1 222220 ..... 3333 ..... 596.35 2 222220 ..... 3330 ..... 254.98 3 222220 ..... 3330 ..... 1547.36 4 222229 ..... 3330 ..... 259.36 5 222229 ..... 33353 ..... 12.36 That the summary sheet layout is A B C 1 222220 3330 (Total of data from column L on other worksheet) 2 222220 3333 3 222220 33353 2 222221 3330 4 222221 3330 5 222221 33353 6 222229 3330 7 222229 3333 8 222229 33353 In C1 to sum all the L-values for the corresponding A and B value =SUMPRODUCT(--(Data!$A$1:$A$1000=A1),--(Data!$B$1:$B$1000=C1), $L$1:$L$1000) This can be copied down the column to complete the table. Adjust the ranges as needed. Beware that SUMPRODUCT cannot use full-column references such as A:A. I duplicated the data in A to make it easier to write and copy the formula. Bt I agree it looks tidier with out. So we will use Conditional formatting. Select all the A after A1 (so A2:A1000 or whatever) column entries Use Format Condition Formatting Set the dialog to Formula is: =A2=A1 and then set the font colour to white (or whatever you cell background is). So now when you have in A2 the same values as in A1 (or if A3 the same as in A2,,,,) the entry will be hidden For more on SUMPRODUCT see http://www.xldynamic.com/source/xld.SUMPRODUCT.html http://mcgimpsey.com/excel/formulae/doubleneg.html best wishes -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with SUMPRODUCT please : )
I think your values in column B may be text, whereas the value you are
trying to match with is a number. A quick way of testing this is to enter your second number as '3330 (i.e. with an apostrophe in front) to see if this corrects matters. Of course, it could be the other way round, with numerical data in your table and your sought value is text. Hope this helps. Pete On Nov 8, 12:38 am, Sierra wrote: Thanks for your patience with my lack of clarity! The formula you have proposed is the same one I've been trying to use, but the total keeps coming out as zero. When I evaluate the calculations, the first part (--(Data!$A$1:$A$1000=A1) seems to be working fine, but the second part (Data!$B$1:$B$1000=C1) comes up as FALSE for all rows, therefore '0', therefore my total on the summary sheet is '0', this is where I'm at a loss. Thanks, Sierra :) "Bernard Liengme" wrote: I will assume the Data sheet (with name Data) looks like: A B C ............ L 1 222220 ..... 3333 ..... 596.35 2 222220 ..... 3330 ..... 254.98 3 222220 ..... 3330 ..... 1547.36 4 222229 ..... 3330 ..... 259.36 5 222229 ..... 33353 ..... 12.36 That the summary sheet layout is A B C 1 222220 3330 (Total of data from column L on other worksheet) 2 222220 3333 3 222220 33353 2 222221 3330 4 222221 3330 5 222221 33353 6 222229 3330 7 222229 3333 8 222229 33353 In C1 to sum all the L-values for the corresponding A and B value =SUMPRODUCT(--(Data!$A$1:$A$1000=A1),--(Data!$B$1:$B$1000=C1), $L$1:$L$1000) This can be copied down the column to complete the table. Adjust the ranges as needed. Beware that SUMPRODUCT cannot use full-column references such as A:A. I duplicated the data in A to make it easier to write and copy the formula. Bt I agree it looks tidier with out. So we will use Conditional formatting. Select all the A after A1 (so A2:A1000 or whatever) column entries Use Format Condition Formatting Set the dialog to Formula is: =A2=A1 and then set the font colour to white (or whatever you cell background is). So now when you have in A2 the same values as in A1 (or if A3 the same as in A2,,,,) the entry will be hidden For more on SUMPRODUCT see http://www.xldynamic.com/source/xld.SUMPRODUCT.html http://mcgimpsey.com/excel/formulae/doubleneg.html best wishes -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
sumproduct? sumif(sumproduct)? | Excel Worksheet Functions | |||
sumproduct | Excel Discussion (Misc queries) | |||
sumproduct | Excel Discussion (Misc queries) | |||
sumproduct | Excel Worksheet Functions | |||
Sumproduct | Excel Discussion (Misc queries) |