Can I SUM column C IF Column A=X AND Column D=Y?
in [Functions]
|
From: Seb on 9 Oct 2009 00:31 Hi, This formula works great whenever there are values in the columns, but when the columns contain formulas I get #N/A. Do you know how to go about this? Seb "RagDyer" wrote: > You're welcome, and thank you for the feed-back. > -- > Regards, > > RD > > --------------------------------------------------------------------------- > Please keep all correspondence within the NewsGroup, so all may benefit ! > --------------------------------------------------------------------------- > "Minx" <Minx(a)discussions.microsoft.com> wrote in message > news:5AB9D85E-BF38-453B-9673-25376BD00B6F(a)microsoft.com... > > That was it! > > (Actually, everything in Row 1 is a text header) > > Thank you so much!! > > > > > > "RagDyeR" wrote: > > > >> You probably have values in B1 to B425 that are *not* true XL recognized > >> numbers! > >> > >> Could B1 be a text header? > > > >
From: T. Valko on 9 Oct 2009 00:42 >when the columns contain formulas I get #N/A. Post the *exact* formula you are trying to use that returns #N/A. -- Biff Microsoft Excel MVP "Seb" <Seb(a)discussions.microsoft.com> wrote in message news:4A761E9C-FDD1-4F6D-B8D4-7A984693811B(a)microsoft.com... > Hi, > > This formula works great whenever there are values in the columns, but > when > the columns contain formulas I get #N/A. Do you know how to go about this? > > Seb > > "RagDyer" wrote: > >> You're welcome, and thank you for the feed-back. >> -- >> Regards, >> >> RD >> >> --------------------------------------------------------------------------- >> Please keep all correspondence within the NewsGroup, so all may benefit ! >> --------------------------------------------------------------------------- >> "Minx" <Minx(a)discussions.microsoft.com> wrote in message >> news:5AB9D85E-BF38-453B-9673-25376BD00B6F(a)microsoft.com... >> > That was it! >> > (Actually, everything in Row 1 is a text header) >> > Thank you so much!! >> > >> > >> > "RagDyeR" wrote: >> > >> >> You probably have values in B1 to B425 that are *not* true XL >> >> recognized >> >> numbers! >> >> >> >> Could B1 be a text header? >> > >> >>
From: Seb on 9 Oct 2009 01:32 This is the formula that returns #N/A: =SUM(IF(('New Code'!$AG$2:$AG$7176=Input!$B$31)*('New Code'!$AI$2:$AI$7176=B10),'New Code'!$AM$2:$AM$7176)) and I enter it as an array formula This one, for example, works: =SUM(IF(('New Code'!$AG$2:$AG$7176=Input!$B$31)*('New Code'!$AF$2:$AF$7176=F7),'New Code'!$AM$2:$AM$7176)) also entered as an array formula Seb "T. Valko" wrote: > >when the columns contain formulas I get #N/A. > > Post the *exact* formula you are trying to use that returns #N/A. > > -- > Biff > Microsoft Excel MVP > > > "Seb" <Seb(a)discussions.microsoft.com> wrote in message > news:4A761E9C-FDD1-4F6D-B8D4-7A984693811B(a)microsoft.com... > > Hi, > > > > This formula works great whenever there are values in the columns, but > > when > > the columns contain formulas I get #N/A. Do you know how to go about this? > > > > Seb > > > > "RagDyer" wrote: > > > >> You're welcome, and thank you for the feed-back. > >> -- > >> Regards, > >> > >> RD > >> > >> --------------------------------------------------------------------------- > >> Please keep all correspondence within the NewsGroup, so all may benefit ! > >> --------------------------------------------------------------------------- > >> "Minx" <Minx(a)discussions.microsoft.com> wrote in message > >> news:5AB9D85E-BF38-453B-9673-25376BD00B6F(a)microsoft.com... > >> > That was it! > >> > (Actually, everything in Row 1 is a text header) > >> > Thank you so much!! > >> > > >> > > >> > "RagDyeR" wrote: > >> > > >> >> You probably have values in B1 to B425 that are *not* true XL > >> >> recognized > >> >> numbers! > >> >> > >> >> Could B1 be a text header? > >> > > >> > >> > > >
From: T. Valko on 9 Oct 2009 02:00 The only thing that's different in those formulas other than the criteria cells is the range AI2:AI7176. Are there any #N/A errors already in that range? If so, can you fix the formulas so they don't return those errors? If you can't or don't want to fix those, what type of data is in that range, text, numbers, both? -- Biff Microsoft Excel MVP "Seb" <Seb(a)discussions.microsoft.com> wrote in message news:4A874A78-8996-4A39-8FDF-F85A51F99A3E(a)microsoft.com... > This is the formula that returns #N/A: > =SUM(IF(('New Code'!$AG$2:$AG$7176=Input!$B$31)*('New > Code'!$AI$2:$AI$7176=B10),'New Code'!$AM$2:$AM$7176)) and I enter it as an > array formula > > This one, for example, works: > =SUM(IF(('New Code'!$AG$2:$AG$7176=Input!$B$31)*('New > Code'!$AF$2:$AF$7176=F7),'New Code'!$AM$2:$AM$7176)) also entered as an > array > formula > > Seb > > > > "T. Valko" wrote: > >> >when the columns contain formulas I get #N/A. >> >> Post the *exact* formula you are trying to use that returns #N/A. >> >> -- >> Biff >> Microsoft Excel MVP >> >> >> "Seb" <Seb(a)discussions.microsoft.com> wrote in message >> news:4A761E9C-FDD1-4F6D-B8D4-7A984693811B(a)microsoft.com... >> > Hi, >> > >> > This formula works great whenever there are values in the columns, but >> > when >> > the columns contain formulas I get #N/A. Do you know how to go about >> > this? >> > >> > Seb >> > >> > "RagDyer" wrote: >> > >> >> You're welcome, and thank you for the feed-back. >> >> -- >> >> Regards, >> >> >> >> RD >> >> >> >> --------------------------------------------------------------------------- >> >> Please keep all correspondence within the NewsGroup, so all may >> >> benefit ! >> >> --------------------------------------------------------------------------- >> >> "Minx" <Minx(a)discussions.microsoft.com> wrote in message >> >> news:5AB9D85E-BF38-453B-9673-25376BD00B6F(a)microsoft.com... >> >> > That was it! >> >> > (Actually, everything in Row 1 is a text header) >> >> > Thank you so much!! >> >> > >> >> > >> >> > "RagDyeR" wrote: >> >> > >> >> >> You probably have values in B1 to B425 that are *not* true XL >> >> >> recognized >> >> >> numbers! >> >> >> >> >> >> Could B1 be a text header? >> >> > >> >> >> >> >> >> >>
From: Seb on 9 Oct 2009 03:00 Thank you so much. I did have one #N/A in the range. "T. Valko" wrote: > The only thing that's different in those formulas other than the criteria > cells is the range AI2:AI7176. > > Are there any #N/A errors already in that range? If so, can you fix the > formulas so they don't return those errors? If you can't or don't want to > fix those, what type of data is in that range, text, numbers, both? > > -- > Biff > Microsoft Excel MVP > > > "Seb" <Seb(a)discussions.microsoft.com> wrote in message > news:4A874A78-8996-4A39-8FDF-F85A51F99A3E(a)microsoft.com... > > This is the formula that returns #N/A: > > =SUM(IF(('New Code'!$AG$2:$AG$7176=Input!$B$31)*('New > > Code'!$AI$2:$AI$7176=B10),'New Code'!$AM$2:$AM$7176)) and I enter it as an > > array formula > > > > This one, for example, works: > > =SUM(IF(('New Code'!$AG$2:$AG$7176=Input!$B$31)*('New > > Code'!$AF$2:$AF$7176=F7),'New Code'!$AM$2:$AM$7176)) also entered as an > > array > > formula > > > > Seb > > > > > > > > "T. Valko" wrote: > > > >> >when the columns contain formulas I get #N/A. > >> > >> Post the *exact* formula you are trying to use that returns #N/A. > >> > >> -- > >> Biff > >> Microsoft Excel MVP > >> > >> > >> "Seb" <Seb(a)discussions.microsoft.com> wrote in message > >> news:4A761E9C-FDD1-4F6D-B8D4-7A984693811B(a)microsoft.com... > >> > Hi, > >> > > >> > This formula works great whenever there are values in the columns, but > >> > when > >> > the columns contain formulas I get #N/A. Do you know how to go about > >> > this? > >> > > >> > Seb > >> > > >> > "RagDyer" wrote: > >> > > >> >> You're welcome, and thank you for the feed-back. > >> >> -- > >> >> Regards, > >> >> > >> >> RD > >> >> > >> >> --------------------------------------------------------------------------- > >> >> Please keep all correspondence within the NewsGroup, so all may > >> >> benefit ! > >> >> --------------------------------------------------------------------------- > >> >> "Minx" <Minx(a)discussions.microsoft.com> wrote in message > >> >> news:5AB9D85E-BF38-453B-9673-25376BD00B6F(a)microsoft.com... > >> >> > That was it! > >> >> > (Actually, everything in Row 1 is a text header) > >> >> > Thank you so much!! > >> >> > > >> >> > > >> >> > "RagDyeR" wrote: > >> >> > > >> >> >> You probably have values in B1 to B425 that are *not* true XL > >> >> >> recognized > >> >> >> numbers! > >> >> >> > >> >> >> Could B1 be a text header? > >> >> > > >> >> > >> >> > >> > >> > >> > > >
|
Next
|
Last
Pages: 1 2 Prev: Finding 2 different letters in a range Next: IF and VLOOKUP formula combined together |