Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel 2007: complex COUNTIFS()
I am trying to do a COUNTIFS() function in which I need to compare the
difference between two numeric cells in the same row. In my spreadsheet, column G and column H contain year values (stored as integers). One of the criteria in my COUNTIFS() needs to check the difference between adjacent cells in these columns, for example, if H4-G4 = 5 or if H100-G100 = 10. I know the "easy" way would be to create a new column containing the differences, but I need to avoid that shortcut. Any ideas? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel 2007: complex COUNTIFS()
It is unclear to me where the criteria comes in. Is it supposed to be Count
if the difference is 5 (or something like that)? -- Regards, PJ Please rate this post using the vote buttons if it was helpful. "David Aukerman" wrote: I am trying to do a COUNTIFS() function in which I need to compare the difference between two numeric cells in the same row. In my spreadsheet, column G and column H contain year values (stored as integers). One of the criteria in my COUNTIFS() needs to check the difference between adjacent cells in these columns, for example, if H4-G4 = 5 or if H100-G100 = 10. I know the "easy" way would be to create a new column containing the differences, but I need to avoid that shortcut. Any ideas? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel 2007: complex COUNTIFS()
PJ,
What I'd like to do is this: =COUNTIFS(H:H - G:G, 5) (There are other criteria I'm checking at the same time, which is why I'm using COUNTIFS().) The idea is that I want to check that the difference between columns H and G is 5. Does that help? --David "PJFry" wrote: It is unclear to me where the criteria comes in. Is it supposed to be Count if the difference is 5 (or something like that)? -- Regards, PJ |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel 2007: complex COUNTIFS()
COUNTIFS can't do that. Try this:
=SUMPRODUCT(--(H1:H25-G1:G25=5)) -- Biff Microsoft Excel MVP "David Aukerman" wrote in message ... PJ, What I'd like to do is this: =COUNTIFS(H:H - G:G, 5) (There are other criteria I'm checking at the same time, which is why I'm using COUNTIFS().) The idea is that I want to check that the difference between columns H and G is 5. Does that help? --David "PJFry" wrote: It is unclear to me where the criteria comes in. Is it supposed to be Count if the difference is 5 (or something like that)? -- Regards, PJ |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel 2007: complex COUNTIFS()
Hi Biff,
Thanks, that helps... I was afraid that COUNTIFS couldn't do that. So if I understand SUMPRODUCT correctly, in order to include the other criteria, I'll have to multiply in those other criteria, like this? =SUMPRODUCT(--(H3:H25-G3:G25=5)*(I3:I25="Excellent")) That seems to work for me. Is there a way to generalize this to include all rows from row 3 onward? (Rows 1 and 2 are labels.) --David "T. Valko" wrote: COUNTIFS can't do that. Try this: =SUMPRODUCT(--(H1:H25-G1:G25=5)) -- Biff Microsoft Excel MVP "David Aukerman" wrote in message ... PJ, What I'd like to do is this: =COUNTIFS(H:H - G:G, 5) (There are other criteria I'm checking at the same time, which is why I'm using COUNTIFS().) The idea is that I want to check that the difference between columns H and G is 5. Does that help? --David "PJFry" wrote: It is unclear to me where the criteria comes in. Is it supposed to be Count if the difference is 5 (or something like that)? -- Regards, PJ |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel 2007: complex COUNTIFS()
=SUMPRODUCT(--(H3:H25-G3:G25=5)*(I3:I25="Excellent"))
Just keep the operators all the same. Try it like this: =SUMPRODUCT(--(H3:H25-G3:G25=5),--(I3:I25="Excellent")) Is there a way to generalize this to include all rows from row 3 onward? Excel 2007 has a lot of rows! The SUMPRODUCT function will calculate *every* cell referenced. So if you reference a lot of empty unused cells you're just wasting resources. =SUMPRODUCT(--(H3:H1048576-G3:G1048576=5),--(I3:I1048576="Excellent")) If you're only using a fraction of all 1,048,576 rows the above is a huge waste of precious resources! -- Biff Microsoft Excel MVP "David Aukerman" wrote in message ... Hi Biff, Thanks, that helps... I was afraid that COUNTIFS couldn't do that. So if I understand SUMPRODUCT correctly, in order to include the other criteria, I'll have to multiply in those other criteria, like this? =SUMPRODUCT(--(H3:H25-G3:G25=5)*(I3:I25="Excellent")) That seems to work for me. Is there a way to generalize this to include all rows from row 3 onward? (Rows 1 and 2 are labels.) --David "T. Valko" wrote: COUNTIFS can't do that. Try this: =SUMPRODUCT(--(H1:H25-G1:G25=5)) -- Biff Microsoft Excel MVP "David Aukerman" wrote in message ... PJ, What I'd like to do is this: =COUNTIFS(H:H - G:G, 5) (There are other criteria I'm checking at the same time, which is why I'm using COUNTIFS().) The idea is that I want to check that the difference between columns H and G is 5. Does that help? --David "PJFry" wrote: It is unclear to me where the criteria comes in. Is it supposed to be Count if the difference is 5 (or something like that)? -- Regards, PJ |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel 2007: complex COUNTIFS()
=SUMPRODUCT(--(H3:H25-G3:G25=5)*(I3:I25="Excellent"))
Just keep the operators all the same. Try it like this: =SUMPRODUCT(--(H3:H25-G3:G25=5),--(I3:I25="Excellent")) I see... is there an advantage to using multiple arrays like you described? (i.e. is it computationally faster?) Excel 2007 has a lot of rows! The SUMPRODUCT function will calculate *every* cell referenced. So if you reference a lot of empty unused cells you're just wasting resources. Yeah, that would be inefficient. :) Any chance that I could reference a cell containing the last row to check? Something like =SUMPRODUCT(--(H3:H&Z1-G3:G&Z1=5,...) where Z1 would contain the last row number to check? I think I might be setting my hopes too high this time. Thanks for all your help, --David |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel 2007: complex COUNTIFS()
is there an advantage to using multiple arrays like you described?
(i.e. is it computationally faster?) Yes, it's slightly faster (in most cases) to calculate. See: http://xldynamic.com/source/xld.SUMPRODUCT.html Any chance that I could reference a cell containing the last row to check? I think I might be setting my hopes too high this time. No problem! Use dynamic ranges. Assumes no empty cells *within* the ranges. In Excel 2007... Formulas tabDefined NamesName Manager Click the New button Name: Range1 Refers to: =Sheet1!$H$3:INDEX(Sheet1!$H:$H,COUNT(Sheet1!$H:$H )+2) OK Repeat this and create 2 more named ranges: Range2 Refers to: =Sheet1!$G$3:INDEX(Sheet1!$G:$G,COUNT(Sheet1!$H:$H )+2) Range3 Refers to: =Sheet1!$I$3:INDEX(Sheet1!$I:$I,COUNT(Sheet1!$H:$H )+2) Use your actual sheet name in the above formulas! Then the SUMPRODUCT formula becomes: =SUMPRODUCT(--(Range1-Range2=5),--(Range3="excellent")) -- Biff Microsoft Excel MVP "David Aukerman" wrote in message ... =SUMPRODUCT(--(H3:H25-G3:G25=5)*(I3:I25="Excellent")) Just keep the operators all the same. Try it like this: =SUMPRODUCT(--(H3:H25-G3:G25=5),--(I3:I25="Excellent")) I see... is there an advantage to using multiple arrays like you described? (i.e. is it computationally faster?) Excel 2007 has a lot of rows! The SUMPRODUCT function will calculate *every* cell referenced. So if you reference a lot of empty unused cells you're just wasting resources. Yeah, that would be inefficient. :) Any chance that I could reference a cell containing the last row to check? Something like =SUMPRODUCT(--(H3:H&Z1-G3:G&Z1=5,...) where Z1 would contain the last row number to check? I think I might be setting my hopes too high this time. Thanks for all your help, --David |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel 2007: complex COUNTIFS()
See:
http://xldynamic.com/source/xld.SUMPRODUCT.html Very interesting reading, thanks! No problem! Use dynamic ranges. Fantastic! I'll give it a shot when I've caught up on my sleep. :) Many thanks, --David |
#10
|
|||
|
|||
Quote:
For example, =SUMPRODUCT(--(D$2:D$49=""),--(E$2:E$49="TEST")) works great, but =SUMPRODUCT(--(B$2:D$49=""),--(E$2:E$49="TEST")) returns #VALUE |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2007 countifs function problem | Excel Discussion (Misc queries) | |||
Countifs to Sumproduct for 2003 - was Simple Countifs.. from Fr | Excel Worksheet Functions | |||
Alternative for =countifs (in 2007) for Excel 2003 | Excel Discussion (Misc queries) | |||
Need function that will work in Excel 2003 like "Countifs" in 2007 | Excel Worksheet Functions | |||
countifs - what is instead in excel 2000??? | Excel Worksheet Functions |