| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sum up columns in different sheet with error check
Thank you very much Toppers!
"Toppers" wrote: By default, it will return 0 (zero) if no match is found. If you want it to return blank (nothing?) then : =IF(SUMIF(Sheet1!$H:$H,CONCATENATE($A$15,$I$2),She et1!$D:$D)=0,"",SUMIF(Sheet1!$H:$H,CONCATENATE($A$ 15,$I$2),Sheet1!$D:$D)) OR =IF(SUMIF(Sheet1!$H:$H,$A$15&$I$2,Sheet1!$D:$D)=0, "",SUMIF(Sheet1!$H:$H,$A$15&$I$2,Sheet1!$D:$D) ) An alternative is: =IF(SUMPRODUCT(--(Sheet1!$H1:$H20=$A$15&$I$2),(Sheet1!$D1:$D20))=0, "",SUMPRODUCT(--(Sheet1!$H1:$H20=$A$15&$I$2),(Sheet1!$D1:$D20))) "zeyneddine" wrote: Thank you Toppers. This works to sum up the two values in Sheet 1. However, can you please expand the formula to include an error check, such that if Sheet 1 does not contain any value in (Column H Row 5) that matches the concatenated output in Sheet 2, the return would be nothing? "Toppers" wrote: =SUMIF(Sheet1!$H:$H,CONCATENATE($A$15,$I$2),Sheet1 !$D:$D) In your original post you used the expression row X which (at least to me) implied that the row was variable. If you stated clearly your requirements, we would have reached an answer much quicker. "I" row 1 was changed to "I" row 2 [ from your original note ... Sheet 2 Column A Row X & Column I Row 1] And the formula DOES sum data in sheet1 "zeyneddine" wrote: The I15 gives me no output whereas the correct output would be 40000. I dont understand why you are offsetting anything in Sheet 2. The data to be summed is in Sheet 1. The values ABC and 123 would be present only once in Sheet 2. It is not a range. "Toppers" wrote: .... place in I15. "zeyneddine" wrote: Can you please explain your function? Let me clarify my question with examples Sheet 1 Column H Row 5 = ABC123; Sheet 1 Column D Row 5 = 1234 Sheet 1 Column H Row 6 = ABC123; Sheet 1 Column D Row 6 = 5678 Sheet 2 Column A Row 15 = ABC Sheet 2 Column I Row 2 = 123 In simple words, IF (Sheet 2 Column A Row 15 & Sheet 2 Column I Row 2) is equal to (Sheet 1 Column H Row 5) and (Sheet 1 Column H Row 6), sum up (Sheet 1 Column D Row 5) and (Sheet 1 Column D Row 6), that is 1234+5678. If (Sheet 2 Column A Row 15 & Sheet 2 Column I Row 2) is not matched with anything on Sheet 1, return nothing. Please help. "Toppers" wrote: If I understand correctly, try: =SUMPRODUCT(--(Sheet1!$A$1:$A$20=Sheet2!I1&Sheet2!$A$1:$A$20)*(S heet1!$D$1:$D$20)) All ranges must be same size i.e. same numbr of rows AND cannot be a column i.e A;A is not allowed. HTH "zeyneddine" wrote: Hi. I have a sheet 1 with columns as: A B C D E F G H I I have sheet 2 with columns as A B C D E F G H I The question is if (Sheet 1 Column H) is the same as (Sheet 2 Column A Row X & Column I Row 1), obtain value in (Sheet 1 Column D, same row) and enter it in a field in Sheet 2. The current setup is =IF(ISERROR(OFFSET(Sheet1!$H$1,MATCH(I$1&$A'X',She et1!$H:$H,0)-1,-4)),0,OFFSET(Sheet1!$H$1,MATCH(I$1&$A'X',Sheet1$H: $H,0)-1,-4)) However, (Sheet 1 Column H) can have more than one value same as (Sheet 2 Column A & Column I Row 1), and I want to sum up the values and enter the sum into a field in Sheet 2. Can anyone assist? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Can I protect columns w/in a "List" using Protect Sheet? | Excel Discussion (Misc queries) | |||
| unhide columns a-c on spread sheet | Setting up and Configuration of Excel | |||
| Selecting Last Sheet | Excel Worksheet Functions | |||
| Columns in excel sheet | Setting up and Configuration of Excel | |||
| Naming & renaming a sheet tab | Excel Worksheet Functions | |||
Hybrid Mode
