Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Complicated Count if and sumif help **Big Question!!!
I need to cout all instances when the following criteria are met
AE7:AE9999 (in the Data sheet) = $C$1 (in the Monthly Summary sheet) and The name in L7:L9999 (in the data sheet) = $A$18 (in the Monthly Summary sheet) **This formula will be copied into several cells to count each person's name I need to add up the $ amounts in Q7:Q9999 for each of those lines (this will be a seperate formula) EX Name #of Complaints $Credited Total Allie 10 100 --------------------------------------------- Then I have another table that counts some if the same things, but seperates them more AE7:AE9999 (in the Data sheet) = $C$1 (in the Monthly Summary sheet) and The name in L7:L9999 (in the data sheet) = $A$18 (in the Monthly Summary sheet) I will need to seperate the reason for the complaint which is in K7:K9999 of the Data sheet. There are only 3 options: pricing, freight, other. Thanks a ton for the help. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Complicated Count if and sumif help **Big Question!!!
You want SUMPRODUCT. It can be used to count/sum based on multiple critiera.
Something like =SUMPRODUCT(--(AE7:AE9999=$C$1),--(L7:L9999=$A$18),Q7:Q9999) If you need to look up text (reason for the complaint), use something like: =INDEX(K7:K9999,SUMPRODUCT(--(AE7:AE9999=$C$1),--(L7:L9999=$A$18),ROW(K1:K9993))) "Jeremy" wrote: I need to cout all instances when the following criteria are met AE7:AE9999 (in the Data sheet) = $C$1 (in the Monthly Summary sheet) and The name in L7:L9999 (in the data sheet) = $A$18 (in the Monthly Summary sheet) **This formula will be copied into several cells to count each person's name I need to add up the $ amounts in Q7:Q9999 for each of those lines (this will be a seperate formula) EX Name #of Complaints $Credited Total Allie 10 100 --------------------------------------------- Then I have another table that counts some if the same things, but seperates them more AE7:AE9999 (in the Data sheet) = $C$1 (in the Monthly Summary sheet) and The name in L7:L9999 (in the data sheet) = $A$18 (in the Monthly Summary sheet) I will need to seperate the reason for the complaint which is in K7:K9999 of the Data sheet. There are only 3 options: pricing, freight, other. Thanks a ton for the help. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Complicated Question | Excel Worksheet Functions | |||
SUMIF Complicated. | Excel Discussion (Misc queries) | |||
Complicated SUMPRODUCT OR SUMIF question? | Excel Discussion (Misc queries) | |||
Complicated Vlookup/count problem | Excel Worksheet Functions | |||
Complicated SUMIF Case | Excel Discussion (Misc queries) |