Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
sumproduct help
Everythi9ng I have looked at has been about multiplying is the below possible?
I have a summary tab and a data tab I want to count all examples in K7:K9999 (Data Tab) when the following conditions are met: !DataAE7=!Summary$C$1 !DataK7=!Summary$A$10 I want to add up the total of $ in !DataQ7:Q9999 when the following conditions are met: !DataAE7=!Summary$C$1 !DataK7=!Summary$A$10 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
sumproduct help
Sumproduct only works if you have equal ranges, so I'm hoping this does what
you'd like. Otherwise, you're looking for countif and sumif... =sumproduct(--(Data!K7:K9999=Summary!A10),--(Data!AE7:AE9999=Summary!A10)) will count those values. If column AE is a text field, it will return with a count. If AE is numerical and K is text, just switch them. If neither, then enter a third column with no criteria: --(Data!AB7:AB9999) For the second, it's =sumproduct(--(Data!K7:K9999=Summary!A10),--(Data!AE7:AE9999=Summary!A10),--(Data!Q7:Q9999) Because column Q is numeric, you will automatically receive a sum. HTH! "Jeremy" wrote: Everythi9ng I have looked at has been about multiplying is the below possible? I have a summary tab and a data tab I want to count all examples in K7:K9999 (Data Tab) when the following conditions are met: !DataAE7=!Summary$C$1 !DataK7=!Summary$A$10 I want to add up the total of $ in !DataQ7:Q9999 when the following conditions are met: !DataAE7=!Summary$C$1 !DataK7=!Summary$A$10 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
sumproduct help
Follow up questions:
I was told to use sumproduct, if you can help with a countif and a sumif, that would be great Do the dashes mean I need to inout something else? I get a #REF error when i type in the first formula "Sean Timmons" wrote: Sumproduct only works if you have equal ranges, so I'm hoping this does what you'd like. Otherwise, you're looking for countif and sumif... =sumproduct(--(Data!K7:K9999=Summary!A10),--(Data!AE7:AE9999=Summary!A10)) will count those values. If column AE is a text field, it will return with a count. If AE is numerical and K is text, just switch them. If neither, then enter a third column with no criteria: --(Data!AB7:AB9999) For the second, it's =sumproduct(--(Data!K7:K9999=Summary!A10),--(Data!AE7:AE9999=Summary!A10),--(Data!Q7:Q9999) Because column Q is numeric, you will automatically receive a sum. HTH! "Jeremy" wrote: Everythi9ng I have looked at has been about multiplying is the below possible? I have a summary tab and a data tab I want to count all examples in K7:K9999 (Data Tab) when the following conditions are met: !DataAE7=!Summary$C$1 !DataK7=!Summary$A$10 I want to add up the total of $ in !DataQ7:Q9999 when the following conditions are met: !DataAE7=!Summary$C$1 !DataK7=!Summary$A$10 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
sumproduct help
You would also need ' around the name, so 'Summary'! instead of Summary!
IF you want where 'Data'!AE7 = 'Summary'!$C$1, then =if('Data'!AE7 = 'Summary'!$C$1,count(K7:K9999)) and =sumif('Data'!AE7,'Summary'!$C$1,'Data'!Q7:Q9999 ) "Jeremy" wrote: Follow up questions: I was told to use sumproduct, if you can help with a countif and a sumif, that would be great Do the dashes mean I need to inout something else? I get a #REF error when i type in the first formula "Sean Timmons" wrote: Sumproduct only works if you have equal ranges, so I'm hoping this does what you'd like. Otherwise, you're looking for countif and sumif... =sumproduct(--(Data!K7:K9999=Summary!A10),--(Data!AE7:AE9999=Summary!A10)) will count those values. If column AE is a text field, it will return with a count. If AE is numerical and K is text, just switch them. If neither, then enter a third column with no criteria: --(Data!AB7:AB9999) For the second, it's =sumproduct(--(Data!K7:K9999=Summary!A10),--(Data!AE7:AE9999=Summary!A10),--(Data!Q7:Q9999) Because column Q is numeric, you will automatically receive a sum. HTH! "Jeremy" wrote: Everythi9ng I have looked at has been about multiplying is the below possible? I have a summary tab and a data tab I want to count all examples in K7:K9999 (Data Tab) when the following conditions are met: !DataAE7=!Summary$C$1 !DataK7=!Summary$A$10 I want to add up the total of $ in !DataQ7:Q9999 when the following conditions are met: !DataAE7=!Summary$C$1 !DataK7=!Summary$A$10 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
sumproduct? sumif(sumproduct)? | Excel Worksheet Functions | |||
sumproduct | New Users to Excel | |||
sumproduct | Excel Discussion (Misc queries) | |||
sumproduct | Excel Discussion (Misc queries) | |||
Sumproduct? | Excel Discussion (Misc queries) |