| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
multiple text criteria sum if function
can anyone tell me whats wrong with this formula and how to fix it?
=sumif(and('[Upsell Monthly Report.xls]COB'!$A:$A,'[Upsell Monthly Report.xls]COB'!$C:$C),(A8,B8),('[Upsell Monthly Report.xls]COB'!$I:$I) |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
multiple text criteria sum if function
What do you want to do with this? Is is possibly a SUMPRODUCT formula?
Since I'm having some SUMPRODUCT difficulties today, I'll leave this to someone else. "datasorter" wrote: can anyone tell me whats wrong with this formula and how to fix it? =sumif(and('[Upsell Monthly Report.xls]COB'!$A:$A,'[Upsell Monthly Report.xls]COB'!$C:$C),(A8,B8),('[Upsell Monthly Report.xls]COB'!$I:$I) |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
multiple text criteria sum if function
Hi,
Im trying to use this function to grab a cell in a different workbook. =sumif(and('[Upsell Monthly Report.xls]COB'!$A:$A,'[Upsell Monthly Report.xls]COB'!$C:$C),(A8,B8),('[Upsell Monthly Report.xls]COB'!$I:$I) I added in the 'and' because I thought it would enable me to have two ranges. (Column A and Column C). The A8 and B8 are the criteria that I need to match within Column A and C in the other workbook. Finally, column I is the colmun on the other sheet that I am pulling the number from. I'm thinking of a possible alternative in place on the end instead of the sum range in Column I. Any help is much appreciated. "Barb Reinhardt" wrote: What do you want to do with this? Is is possibly a SUMPRODUCT formula? Since I'm having some SUMPRODUCT difficulties today, I'll leave this to someone else. "datasorter" wrote: can anyone tell me whats wrong with this formula and how to fix it? =sumif(and('[Upsell Monthly Report.xls]COB'!$A:$A,'[Upsell Monthly Report.xls]COB'!$C:$C),(A8,B8),('[Upsell Monthly Report.xls]COB'!$I:$I) |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
multiple text criteria sum if function
Put into words what you're trying to do.
-- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "datasorter" wrote in message ... can anyone tell me whats wrong with this formula and how to fix it? =sumif(and('[Upsell Monthly Report.xls]COB'!$A:$A,'[Upsell Monthly Report.xls]COB'!$C:$C),(A8,B8),('[Upsell Monthly Report.xls]COB'!$I:$I) |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
multiple text criteria sum if function
Thinks it's your criteria A8,B8 as it's not a range and A8 is in Column A which is a circular ref. VBA Noob -- VBA Noob ------------------------------------------------------------------------ VBA Noob's Profile: http://www.excelforum.com/member.php...o&userid=33833 View this thread: http://www.excelforum.com/showthread...hreadid=566150 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
multiple text criteria sum if function
1. SUMIF does not work if the file is closed (same goes for COUNTIF)
2. Your criteria is a bit unclear, do you mean either A8 or B8 3. You can use SUMPRODUCT but you cannot use the whole column like A:A, you need a specified range, here are 2 formulas that should work, I was too lazy to use your path but the formulas work with closed workbooks =SUMPRODUCT(--((COB!A2:A5000=A8)+(COB!A2:A5000=B8)0),COB!I2:I50 00) or =SUMPRODUCT((COB!A2:A5000=A8:B8)*(COB!I2:I5000)) the former is more stable since it won't choke if there is an occasional text value in the range -- Regards, Peo Sjoblom Excel 95 - Excel 2007 Northwest Excel Solutions www.nwexcelsolutions.com I will leave out the path and workbook name, you can add that yourself but "datasorter" wrote in message ... can anyone tell me whats wrong with this formula and how to fix it? =sumif(and('[Upsell Monthly Report.xls]COB'!$A:$A,'[Upsell Monthly Report.xls]COB'!$C:$C),(A8,B8),('[Upsell Monthly Report.xls]COB'!$I:$I) |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
multiple text criteria sum if function
I tried this and I got #N/A
=SUMPRODUCT(--(('[Upsell Monthly Report.xls]COB'!$A$19:$A$944=A8)+('[Upsell Monthly Report.xls]COB'!$C$19:$C$891=C8)0),'[Upsell Monthly Report.xls]COB'!$I$19:$I$1075) |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
multiple text criteria sum if function
Your ranges are *not equal*!
They *must all* be exactly the same size. A19:A944 C19:C891 I19:I1075 Pick whatever range best suits your data, and equalize them ... THEN ... try again! -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "datasorter" wrote in message ... I tried this and I got #N/A =SUMPRODUCT(--(('[Upsell Monthly Report.xls]COB'!$A$19:$A$944=A8)+('[Upsell Monthly Report.xls]COB'!$C$19:$C$891=C8)0),'[Upsell Monthly Report.xls]COB'!$I$19:$I$1075) |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
multiple text criteria sum if function
True but my formula was wrong since I didn't observe that the OP used the A,
the C and the I range, so it would be a regular sumproduct not the OR version I cooked up so =SUMPRODUCT(--('[Upsell Monthly Report.xls]COB'!$A$19:$A$1075=A8),--('[Upsell Monthly Report.xls]COB'!$C$19:$C$1075=C8),'[Upsell Monthly Report.xls]COB'!$I$19:$I$1075) might work -- Regards, Peo Sjoblom Excel 95 - Excel 2007 Northwest Excel Solutions www.nwexcelsolutions.com "Ragdyer" wrote in message ... Your ranges are *not equal*! They *must all* be exactly the same size. A19:A944 C19:C891 I19:I1075 Pick whatever range best suits your data, and equalize them ... THEN ... try again! -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "datasorter" wrote in message ... I tried this and I got #N/A =SUMPRODUCT(--(('[Upsell Monthly Report.xls]COB'!$A$19:$A$944=A8)+('[Upsell Monthly Report.xls]COB'!$C$19:$C$891=C8)0),'[Upsell Monthly Report.xls]COB'!$I$19:$I$1075) |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
multiple text criteria sum if function
It's *still* not clear (to me) exactly what criteria refers to what range.
The way the OP formula was constructed, anything is (was) possible.<g -- Regards, RD ---------------------------------------------------------------------------- ------------------- Please keep all correspondence within the Group, so all may benefit ! ---------------------------------------------------------------------------- ------------------- "Peo Sjoblom" wrote in message ... True but my formula was wrong since I didn't observe that the OP used the A, the C and the I range, so it would be a regular sumproduct not the OR version I cooked up so =SUMPRODUCT(--('[Upsell Monthly Report.xls]COB'!$A$19:$A$1075=A8),--('[Upsell Monthly Report.xls]COB'!$C$19:$C$1075=C8),'[Upsell Monthly Report.xls]COB'!$I$19:$I$1075) might work -- Regards, Peo Sjoblom Excel 95 - Excel 2007 Northwest Excel Solutions www.nwexcelsolutions.com "Ragdyer" wrote in message ... Your ranges are *not equal*! They *must all* be exactly the same size. A19:A944 C19:C891 I19:I1075 Pick whatever range best suits your data, and equalize them ... THEN ... try again! -- HTH, RD -------------------------------------------------------------------------- - Please keep all correspondence within the NewsGroup, so all may benefit ! -------------------------------------------------------------------------- - "datasorter" wrote in message ... I tried this and I got #N/A =SUMPRODUCT(--(('[Upsell Monthly Report.xls]COB'!$A$19:$A$944=A8)+('[Upsell Monthly Report.xls]COB'!$C$19:$C$891=C8)0),'[Upsell Monthly Report.xls]COB'!$I$19:$I$1075) |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
multiple text criteria sum if function
Try:
=SUMPRODUCT(--('[Upsell Monthly Report.xls]COB'!$A1:$A1000=A8),--('[Upsell Monthly Report.xls]COB'!$C1:$C1000=B8)*('[Upsell Monthly Report.xls]COB'!$I1:$I000)) SUMPRODUCT must have defined arrays i.e. cannot use whole columns, so adjust to you needs. All ranges must be same size. HTH "datasorter" wrote: can anyone tell me whats wrong with this formula and how to fix it? =sumif(and('[Upsell Monthly Report.xls]COB'!$A:$A,'[Upsell Monthly Report.xls]COB'!$C:$C),(A8,B8),('[Upsell Monthly Report.xls]COB'!$I:$I) |
|
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
multiple text criteria sum if function
I changed your '$I1:$I000' to '$I1:$I1000' and I got #REF!
"Toppers" wrote: Try: =SUMPRODUCT(--('[Upsell Monthly Report.xls]COB'!$A1:$A1000=A8),--('[Upsell Monthly Report.xls]COB'!$C1:$C1000=B8)*('[Upsell Monthly Report.xls]COB'!$I1:$I000)) SUMPRODUCT must have defined arrays i.e. cannot use whole columns, so adjust to you needs. All ranges must be same size. HTH "datasorter" wrote: can anyone tell me whats wrong with this formula and how to fix it? =sumif(and('[Upsell Monthly Report.xls]COB'!$A:$A,'[Upsell Monthly Report.xls]COB'!$C:$C),(A8,B8),('[Upsell Monthly Report.xls]COB'!$I:$I) |
|
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
multiple text criteria sum if function
Check all references e.g Workbook names are correct. I tried using your
w/book name and worked fine. "datasorter" wrote: I changed your '$I1:$I000' to '$I1:$I1000' and I got #REF! "Toppers" wrote: Try: =SUMPRODUCT(--('[Upsell Monthly Report.xls]COB'!$A1:$A1000=A8),--('[Upsell Monthly Report.xls]COB'!$C1:$C1000=B8)*('[Upsell Monthly Report.xls]COB'!$I1:$I000)) SUMPRODUCT must have defined arrays i.e. cannot use whole columns, so adjust to you needs. All ranges must be same size. HTH "datasorter" wrote: can anyone tell me whats wrong with this formula and how to fix it? =sumif(and('[Upsell Monthly Report.xls]COB'!$A:$A,'[Upsell Monthly Report.xls]COB'!$C:$C),(A8,B8),('[Upsell Monthly Report.xls]COB'!$I:$I) |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Sumif function with multiple criteria | Excel Worksheet Functions | |||
| Justify text across multiple columns | Excel Discussion (Misc queries) | |||
| creating a function with multiple criteria | Excel Worksheet Functions | |||
| Counting by multiple criteria | Excel Worksheet Functions | |||
| COUNTIF or SUM function (Multiple criteria) HELP!! | Excel Worksheet Functions | |||
Hybrid Mode
