Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct with conditions - urgent help needed
I have a sumproduct in my spreadsheet =sumproduct(N8:N13,I8:I13) that works
fine. However, I need to add a condition to this. If F8:F13="XYZ". How do I change the sumproduct to only calculate if column F contains "XYZ"? Your help would be greatly appreciated - under a tight deadline for a client! Jana |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct with conditions - urgent help needed
Try this:
=SUMPRODUCT(--(F8:F13="XYZ"),I8:I13,N8:N13) -- Biff Microsoft Excel MVP "JANA" wrote in message ... I have a sumproduct in my spreadsheet =sumproduct(N8:N13,I8:I13) that works fine. However, I need to add a condition to this. If F8:F13="XYZ". How do I change the sumproduct to only calculate if column F contains "XYZ"? Your help would be greatly appreciated - under a tight deadline for a client! Jana |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct with conditions - urgent help needed
Hope this works =if(sum(F8:F13)=xyz,sum(F8:F13),(Sum(F8:F13,I8:I13 ))) Thanks Venkatesan |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct with conditions - urgent help needed
No it won't work, Venkatesan.
You snipped the question, which was: "I have a sumproduct in my spreadsheet =sumproduct(N8:N13,I8:I13) that works fine. However, I need to add a condition to this. If F8:F13="XYZ". How do I change the sumproduct to only calculate if column F contains "XYZ"?" There are a number of things wrong with your answer: Firstly, if you want to compare with a text string like XYZ you need to enclose it in double quotes. Secondly a SUM() formula will return a number, not a text string, so your IF test would always fail. Thirdly, the OP wanted an answer which was not a SUM of columns F and I, but a SUMPRODUCT of columns N and I. Fourthly, the OP wanted the SUMPRODUCT calculated for the rows where column F contains "xyz", and not for the other rows. Fifthly, and less important, you don't need parentheses around your final SUM() function. The answer which the OP needed is either =SUMPRODUCT(--(F8:F13="xyz"),N8:N13,I8:I13) or =SUMPRODUCT((F8:F13="xyz")*N8:N13*I8:I13) -- David Biddulph Venkatesan wrote: Hope this works =if(sum(F8:F13)=xyz,sum(F8:F13),(Sum(F8:F13,I8:I13 ))) Thanks Venkatesan |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
URGENT HELP NEEDED | Excel Discussion (Misc queries) | |||
Urgent help needed | Excel Worksheet Functions | |||
Urgent Help Needed please | Excel Worksheet Functions | |||
Help Needed Urgent | Excel Discussion (Misc queries) | |||
Urgent help needed | Excel Worksheet Functions |