|
SUMPRODUCT Formula Help Please
First thank you for looking at this and helping and soory for the long post.
I'm not sure if I can relay what I'm doing without the spreadsheet but what I have is this. I have about 700 rows of data with many columns, two columns of information I want to use are vendor name (column G) and file name (column A). What I am trying to do is use the file name as one filter and then count how many vendors are listed with that same file name (vendors can be listed multiple times but I only what to count each one once, there can also be a blank cell for no vendor listed). I have tried this formula, =SUMPRODUCT(--($A$4:$A$658='Summary Data'!E1),(1/COUNTIF($G$4:$G$658,$G$4:$G$658&""))) and it works fine but when I use the same formula like this, =SUMPRODUCT(--($A$4:$A$658='Summary Data'!G1),(1/COUNTIF($G$4:$G$658,$G$4:$G$658&""))) I get 13.177 but it should be 27. Is there way a formula like this can work? TIA Joe |
SUMPRODUCT Formula Help Please
Why don't you create a pivot table?
File name would be the column and drop the vendor in the data field and have the count function count the number of vendors per file name? If this doesn't address your needs, please repost. Dave -- A hint to posters: Specific, detailed questions are more likely to be answered than questions that provide no detail about your problem. "Joe Gieder" wrote: First thank you for looking at this and helping and soory for the long post. I'm not sure if I can relay what I'm doing without the spreadsheet but what I have is this. I have about 700 rows of data with many columns, two columns of information I want to use are vendor name (column G) and file name (column A). What I am trying to do is use the file name as one filter and then count how many vendors are listed with that same file name (vendors can be listed multiple times but I only what to count each one once, there can also be a blank cell for no vendor listed). I have tried this formula, =SUMPRODUCT(--($A$4:$A$658='Summary Data'!E1),(1/COUNTIF($G$4:$G$658,$G$4:$G$658&""))) and it works fine but when I use the same formula like this, =SUMPRODUCT(--($A$4:$A$658='Summary Data'!G1),(1/COUNTIF($G$4:$G$658,$G$4:$G$658&""))) I get 13.177 but it should be 27. Is there way a formula like this can work? TIA Joe |
SUMPRODUCT Formula Help Please
Try...
=COUNT(1/FREQUENCY(IF($A$4:$A$658='Summary Data'!E1,IF($G$4:$G$658<"",MATCH($G$4:$G$658,$G$4 :$G$658,0))),ROW($G$4:$ G$658)-ROW($G$4)+1)) ....confirmed with CONTROL+SHIFT+ENTER, not just ENTER. Hope this helps! In article , Joe Gieder wrote: First thank you for looking at this and helping and soory for the long post. I'm not sure if I can relay what I'm doing without the spreadsheet but what I have is this. I have about 700 rows of data with many columns, two columns of information I want to use are vendor name (column G) and file name (column A). What I am trying to do is use the file name as one filter and then count how many vendors are listed with that same file name (vendors can be listed multiple times but I only what to count each one once, there can also be a blank cell for no vendor listed). I have tried this formula, =SUMPRODUCT(--($A$4:$A$658='Summary Data'!E1),(1/COUNTIF($G$4:$G$658,$G$4:$G$658&""))) and it works fine but when I use the same formula like this, =SUMPRODUCT(--($A$4:$A$658='Summary Data'!G1),(1/COUNTIF($G$4:$G$658,$G$4:$G$658&""))) I get 13.177 but it should be 27. Is there way a formula like this can work? TIA Joe |
SUMPRODUCT Formula Help Please
I didn't think of a pivot table.
I just tried the pivot table and it counts all the vendors (even the dups), I need to count each one once only, if it appears three times it only counts as one. Another thing I do is after I get this result I use the total number of unique vendors and find out how many have responded and divide these two numbers to come up with a % of how may are complete. Thanks for you help Joe "Dave F" wrote: Why don't you create a pivot table? File name would be the column and drop the vendor in the data field and have the count function count the number of vendors per file name? If this doesn't address your needs, please repost. Dave -- A hint to posters: Specific, detailed questions are more likely to be answered than questions that provide no detail about your problem. "Joe Gieder" wrote: First thank you for looking at this and helping and soory for the long post. I'm not sure if I can relay what I'm doing without the spreadsheet but what I have is this. I have about 700 rows of data with many columns, two columns of information I want to use are vendor name (column G) and file name (column A). What I am trying to do is use the file name as one filter and then count how many vendors are listed with that same file name (vendors can be listed multiple times but I only what to count each one once, there can also be a blank cell for no vendor listed). I have tried this formula, =SUMPRODUCT(--($A$4:$A$658='Summary Data'!E1),(1/COUNTIF($G$4:$G$658,$G$4:$G$658&""))) and it works fine but when I use the same formula like this, =SUMPRODUCT(--($A$4:$A$658='Summary Data'!G1),(1/COUNTIF($G$4:$G$658,$G$4:$G$658&""))) I get 13.177 but it should be 27. Is there way a formula like this can work? TIA Joe |
SUMPRODUCT Formula Help Please
Hm, I missed the requirement of dupes.
1) Concatenate the vendor and file names in a helper column so that you can filter on this helper column. =CONCATENATE(A1,B1) if the vendor and file name are in column A and column B 2) Apply the advanved filter tool to extract unique records. See here for instructions: http://www.contextures.com/xladvfilter01.html Scroll half way down to "Filter Unique Records" 3) Create a pivot table off the filtered records. Dave -- A hint to posters: Specific, detailed questions are more likely to be answered than questions that provide no detail about your problem. "Joe Gieder" wrote: I didn't think of a pivot table. I just tried the pivot table and it counts all the vendors (even the dups), I need to count each one once only, if it appears three times it only counts as one. Another thing I do is after I get this result I use the total number of unique vendors and find out how many have responded and divide these two numbers to come up with a % of how may are complete. Thanks for you help Joe "Dave F" wrote: Why don't you create a pivot table? File name would be the column and drop the vendor in the data field and have the count function count the number of vendors per file name? If this doesn't address your needs, please repost. Dave -- A hint to posters: Specific, detailed questions are more likely to be answered than questions that provide no detail about your problem. "Joe Gieder" wrote: First thank you for looking at this and helping and soory for the long post. I'm not sure if I can relay what I'm doing without the spreadsheet but what I have is this. I have about 700 rows of data with many columns, two columns of information I want to use are vendor name (column G) and file name (column A). What I am trying to do is use the file name as one filter and then count how many vendors are listed with that same file name (vendors can be listed multiple times but I only what to count each one once, there can also be a blank cell for no vendor listed). I have tried this formula, =SUMPRODUCT(--($A$4:$A$658='Summary Data'!E1),(1/COUNTIF($G$4:$G$658,$G$4:$G$658&""))) and it works fine but when I use the same formula like this, =SUMPRODUCT(--($A$4:$A$658='Summary Data'!G1),(1/COUNTIF($G$4:$G$658,$G$4:$G$658&""))) I get 13.177 but it should be 27. Is there way a formula like this can work? TIA Joe |
SUMPRODUCT Formula Help Please
Thanks Dominic, this worked and was what I was looking for. Now can I take
this one step further by including $J$4:$J$658<"" to count only the ones that have a price? I tried by putting =COUNT(1/FREQUENCY(IF(AND($A$4:$A$658='Summary Data'!E1,$J$4:$J$658<""),IF($G$4:$G$658<"",MATCH ($G$4:$G$658,$G$4:$G$658,0))),ROW($G$4:$ G$658)-ROW($G$4)+1)) but it didn't work. I also tried other various places but to no avail. Thank you Joe "Domenic" wrote: Try... =COUNT(1/FREQUENCY(IF($A$4:$A$658='Summary Data'!E1,IF($G$4:$G$658<"",MATCH($G$4:$G$658,$G$4 :$G$658,0))),ROW($G$4:$ G$658)-ROW($G$4)+1)) ....confirmed with CONTROL+SHIFT+ENTER, not just ENTER. Hope this helps! In article , Joe Gieder wrote: First thank you for looking at this and helping and soory for the long post. I'm not sure if I can relay what I'm doing without the spreadsheet but what I have is this. I have about 700 rows of data with many columns, two columns of information I want to use are vendor name (column G) and file name (column A). What I am trying to do is use the file name as one filter and then count how many vendors are listed with that same file name (vendors can be listed multiple times but I only what to count each one once, there can also be a blank cell for no vendor listed). I have tried this formula, =SUMPRODUCT(--($A$4:$A$658='Summary Data'!E1),(1/COUNTIF($G$4:$G$658,$G$4:$G$658&""))) and it works fine but when I use the same formula like this, =SUMPRODUCT(--($A$4:$A$658='Summary Data'!G1),(1/COUNTIF($G$4:$G$658,$G$4:$G$658&""))) I get 13.177 but it should be 27. Is there way a formula like this can work? TIA Joe |
SUMPRODUCT Formula Help Please
Dave,
Thanks for your help but the pivot table just wasn't what I was looking for but it seems like a viable sollution. Thank you for your help Joe "Dave F" wrote: Hm, I missed the requirement of dupes. 1) Concatenate the vendor and file names in a helper column so that you can filter on this helper column. =CONCATENATE(A1,B1) if the vendor and file name are in column A and column B 2) Apply the advanved filter tool to extract unique records. See here for instructions: http://www.contextures.com/xladvfilter01.html Scroll half way down to "Filter Unique Records" 3) Create a pivot table off the filtered records. Dave -- A hint to posters: Specific, detailed questions are more likely to be answered than questions that provide no detail about your problem. "Joe Gieder" wrote: I didn't think of a pivot table. I just tried the pivot table and it counts all the vendors (even the dups), I need to count each one once only, if it appears three times it only counts as one. Another thing I do is after I get this result I use the total number of unique vendors and find out how many have responded and divide these two numbers to come up with a % of how may are complete. Thanks for you help Joe "Dave F" wrote: Why don't you create a pivot table? File name would be the column and drop the vendor in the data field and have the count function count the number of vendors per file name? If this doesn't address your needs, please repost. Dave -- A hint to posters: Specific, detailed questions are more likely to be answered than questions that provide no detail about your problem. "Joe Gieder" wrote: First thank you for looking at this and helping and soory for the long post. I'm not sure if I can relay what I'm doing without the spreadsheet but what I have is this. I have about 700 rows of data with many columns, two columns of information I want to use are vendor name (column G) and file name (column A). What I am trying to do is use the file name as one filter and then count how many vendors are listed with that same file name (vendors can be listed multiple times but I only what to count each one once, there can also be a blank cell for no vendor listed). I have tried this formula, =SUMPRODUCT(--($A$4:$A$658='Summary Data'!E1),(1/COUNTIF($G$4:$G$658,$G$4:$G$658&""))) and it works fine but when I use the same formula like this, =SUMPRODUCT(--($A$4:$A$658='Summary Data'!G1),(1/COUNTIF($G$4:$G$658,$G$4:$G$658&""))) I get 13.177 but it should be 27. Is there way a formula like this can work? TIA Joe |
SUMPRODUCT Formula Help Please
The AND function only returns a single result. So it won't work with
arrays. Try... =COUNT(1/FREQUENCY(IF($A$4:$A$658='Summary Data'!E1,IF($J$4:$J$658<"",IF($G$4:$G$658<"",MAT CH($G$4:$G$658,$G$4:$G$ 658,0)))),ROW($G$4:$G$658)-ROW($G$4)+1)) ....confirmed with CONTROL+SHIFT+ENTER. Hope this helps! In article , Joe Gieder wrote: Thanks Dominic, this worked and was what I was looking for. Now can I take this one step further by including $J$4:$J$658<"" to count only the ones that have a price? I tried by putting =COUNT(1/FREQUENCY(IF(AND($A$4:$A$658='Summary Data'!E1,$J$4:$J$658<""),IF($G$4:$G$658<"",MATCH ($G$4:$G$658,$G$4:$G$658,0)) ),ROW($G$4:$ G$658)-ROW($G$4)+1)) but it didn't work. I also tried other various places but to no avail. Thank you Joe |
SUMPRODUCT Formula Help Please
=COUNTIF($A$4:$A$658,'Summary Data'!E1)
=COUNTIF($A$4:$A$658,'Summary Data'!G1) -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Joe Gieder" wrote in message ... First thank you for looking at this and helping and soory for the long post. I'm not sure if I can relay what I'm doing without the spreadsheet but what I have is this. I have about 700 rows of data with many columns, two columns of information I want to use are vendor name (column G) and file name (column A). What I am trying to do is use the file name as one filter and then count how many vendors are listed with that same file name (vendors can be listed multiple times but I only what to count each one once, there can also be a blank cell for no vendor listed). I have tried this formula, =SUMPRODUCT(--($A$4:$A$658='Summary Data'!E1),(1/COUNTIF($G$4:$G$658,$G$4:$G$658&""))) and it works fine but when I use the same formula like this, =SUMPRODUCT(--($A$4:$A$658='Summary Data'!G1),(1/COUNTIF($G$4:$G$658,$G$4:$G$658&""))) I get 13.177 but it should be 27. Is there way a formula like this can work? TIA Joe |
SUMPRODUCT Formula Help Please
Thank you again for the help, it worked perfectly.
"Domenic" wrote: The AND function only returns a single result. So it won't work with arrays. Try... =COUNT(1/FREQUENCY(IF($A$4:$A$658='Summary Data'!E1,IF($J$4:$J$658<"",IF($G$4:$G$658<"",MAT CH($G$4:$G$658,$G$4:$G$ 658,0)))),ROW($G$4:$G$658)-ROW($G$4)+1)) ....confirmed with CONTROL+SHIFT+ENTER. Hope this helps! In article , Joe Gieder wrote: Thanks Dominic, this worked and was what I was looking for. Now can I take this one step further by including $J$4:$J$658<"" to count only the ones that have a price? I tried by putting =COUNT(1/FREQUENCY(IF(AND($A$4:$A$658='Summary Data'!E1,$J$4:$J$658<""),IF($G$4:$G$658<"",MATCH ($G$4:$G$658,$G$4:$G$658,0)) ),ROW($G$4:$ G$658)-ROW($G$4)+1)) but it didn't work. I also tried other various places but to no avail. Thank you Joe |
SUMPRODUCT Formula Help Please
Bob,
It works to count all the vendors but I only need to count the unique names. Thank you for your help. Joe "Bob Phillips" wrote: =COUNTIF($A$4:$A$658,'Summary Data'!E1) =COUNTIF($A$4:$A$658,'Summary Data'!G1) -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Joe Gieder" wrote in message ... First thank you for looking at this and helping and soory for the long post. I'm not sure if I can relay what I'm doing without the spreadsheet but what I have is this. I have about 700 rows of data with many columns, two columns of information I want to use are vendor name (column G) and file name (column A). What I am trying to do is use the file name as one filter and then count how many vendors are listed with that same file name (vendors can be listed multiple times but I only what to count each one once, there can also be a blank cell for no vendor listed). I have tried this formula, =SUMPRODUCT(--($A$4:$A$658='Summary Data'!E1),(1/COUNTIF($G$4:$G$658,$G$4:$G$658&""))) and it works fine but when I use the same formula like this, =SUMPRODUCT(--($A$4:$A$658='Summary Data'!G1),(1/COUNTIF($G$4:$G$658,$G$4:$G$658&""))) I get 13.177 but it should be 27. Is there way a formula like this can work? TIA Joe |
SUMPRODUCT Formula Help Please
Domenic wrote...
The AND function only returns a single result. So it won't work with arrays. Try... =COUNT(1/FREQUENCY(IF($A$4:$A$658='Summary Data'!E1, IF($J$4:$J$658<"",IF($G$4:$G$658<"", MATCH($G$4:$G$658,$G$4:$G$658,0)))),ROW($G$4:$G$6 58)-ROW($G$4)+1)) .... Lots of IF calls. They may improve recalc speed, but they limit generality by using up nested function call levels. A possibly slower alternative, =COUNT(1/FREQUENCY(IF(($A$4:$A$658='Summary Data'!E1) *($J$4:$J$658<"")*($G$4:$G$658<""), MATCH($G$4:$G$658,$G$4:$G$658,0)),ROW($G$4:$G$658)-ROW($G$4)+1)) allows for a greater number of conditions, subject to the formula length limit. |
SUMPRODUCT Formula Help Please
I'm not so sure I'd be willing to sacrifice efficiency in favour of
generality. I'm guessing that in some instances the difference in efficiency can be significant. In article . com, "Harlan Grove" wrote: Domenic wrote... The AND function only returns a single result. So it won't work with arrays. Try... =COUNT(1/FREQUENCY(IF($A$4:$A$658='Summary Data'!E1, IF($J$4:$J$658<"",IF($G$4:$G$658<"", MATCH($G$4:$G$658,$G$4:$G$658,0)))),ROW($G$4:$G$6 58)-ROW($G$4)+1)) ... Lots of IF calls. They may improve recalc speed, but they limit generality by using up nested function call levels. A possibly slower alternative, =COUNT(1/FREQUENCY(IF(($A$4:$A$658='Summary Data'!E1) *($J$4:$J$658<"")*($G$4:$G$658<""), MATCH($G$4:$G$658,$G$4:$G$658,0)),ROW($G$4:$G$658)-ROW($G$4)+1)) allows for a greater number of conditions, subject to the formula length limit. |
| All times are GMT +1. The time now is 06:06 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com