Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct help
Repost for clarification and hopefully a quicker answer....
Sorry for the repost I tried to calrify yesterday but probably better to start all over My data is in A1:G12 I need a formula that will find the max of B2:G2, then in that column count the cells 0 where the value in ColA is DOG. something like countif(max(b2:G2),A:A="DOG") I believe it will take sumproduct to accomplish but I can not figure it out. Thanks! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct help
Try something like this:
Assuming Row_1 contains headings and the actual data is in A2:G12 This formula returns the count of cell values 0 where the corresponding Col_A value is "DOG" in the column containing the max Row_2 value =SUMPRODUCT((A3:A12="DOG")*(B2:G2=MAX(B2:G2))*(B3: G120)) Does that help? *********** Regards, Ron XL2002, WinXP "Sandy" wrote: Repost for clarification and hopefully a quicker answer.... Sorry for the repost I tried to calrify yesterday but probably better to start all over My data is in A1:G12 I need a formula that will find the max of B2:G2, then in that column count the cells 0 where the value in ColA is DOG. something like countif(max(b2:G2),A:A="DOG") I believe it will take sumproduct to accomplish but I can not figure it out. Thanks! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct help
This is close. ColA contains DOG1 DOG2, DOG3 etc. Is the only way to catch
all of the dogs LOL to modify by adding iterations of the original formula and inserting DOG1, DOG2,DOG3 etc since apprently wildcards do not work in a SUMPRODUCT formula. Thanks! "Ron Coderre" wrote: Try something like this: Assuming Row_1 contains headings and the actual data is in A2:G12 This formula returns the count of cell values 0 where the corresponding Col_A value is "DOG" in the column containing the max Row_2 value =SUMPRODUCT((A3:A12="DOG")*(B2:G2=MAX(B2:G2))*(B3: G120)) Does that help? *********** Regards, Ron XL2002, WinXP "Sandy" wrote: Repost for clarification and hopefully a quicker answer.... Sorry for the repost I tried to calrify yesterday but probably better to start all over My data is in A1:G12 I need a formula that will find the max of B2:G2, then in that column count the cells 0 where the value in ColA is DOG. something like countif(max(b2:G2),A:A="DOG") I believe it will take sumproduct to accomplish but I can not figure it out. Thanks! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct help
Would this work for you?
=SUMPRODUCT((LEFT(A3:A12,3)="DOG")*(B2:G2=MAX(B2:G 2))*(B3:G120)) Does that help? *********** Regards, Ron XL2002, WinXP "Sandy" wrote: This is close. ColA contains DOG1 DOG2, DOG3 etc. Is the only way to catch all of the dogs LOL to modify by adding iterations of the original formula and inserting DOG1, DOG2,DOG3 etc since apprently wildcards do not work in a SUMPRODUCT formula. Thanks! "Ron Coderre" wrote: Try something like this: Assuming Row_1 contains headings and the actual data is in A2:G12 This formula returns the count of cell values 0 where the corresponding Col_A value is "DOG" in the column containing the max Row_2 value =SUMPRODUCT((A3:A12="DOG")*(B2:G2=MAX(B2:G2))*(B3: G120)) Does that help? *********** Regards, Ron XL2002, WinXP "Sandy" wrote: Repost for clarification and hopefully a quicker answer.... Sorry for the repost I tried to calrify yesterday but probably better to start all over My data is in A1:G12 I need a formula that will find the max of B2:G2, then in that column count the cells 0 where the value in ColA is DOG. something like countif(max(b2:G2),A:A="DOG") I believe it will take sumproduct to accomplish but I can not figure it out. Thanks! |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct help
Absolutely!
Thanks Ron "Ron Coderre" wrote: Would this work for you? =SUMPRODUCT((LEFT(A3:A12,3)="DOG")*(B2:G2=MAX(B2:G 2))*(B3:G120)) Does that help? *********** Regards, Ron XL2002, WinXP "Sandy" wrote: This is close. ColA contains DOG1 DOG2, DOG3 etc. Is the only way to catch all of the dogs LOL to modify by adding iterations of the original formula and inserting DOG1, DOG2,DOG3 etc since apprently wildcards do not work in a SUMPRODUCT formula. Thanks! "Ron Coderre" wrote: Try something like this: Assuming Row_1 contains headings and the actual data is in A2:G12 This formula returns the count of cell values 0 where the corresponding Col_A value is "DOG" in the column containing the max Row_2 value =SUMPRODUCT((A3:A12="DOG")*(B2:G2=MAX(B2:G2))*(B3: G120)) Does that help? *********** Regards, Ron XL2002, WinXP "Sandy" wrote: Repost for clarification and hopefully a quicker answer.... Sorry for the repost I tried to calrify yesterday but probably better to start all over My data is in A1:G12 I need a formula that will find the max of B2:G2, then in that column count the cells 0 where the value in ColA is DOG. something like countif(max(b2:G2),A:A="DOG") I believe it will take sumproduct to accomplish but I can not figure it out. Thanks! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
using sumproduct | Excel Discussion (Misc queries) | |||
SUMPRODUCT? | Excel Discussion (Misc queries) | |||
SUMPRODUCT | Excel Discussion (Misc queries) | |||
sumproduct ? | Excel Worksheet Functions | |||
Sumproduct Help | Excel Worksheet Functions |