Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Please help w/Sumproduct!
Hi,
Looking for a Sumproduct formula that will add the quantities in Col. C that have (A in Col. A & "whatever" in Col. B) + Only those Quantities in Col. C that have (Blanks in Col. A & Non-Blanks in Col. B) Col. A Col. B Col. C F Blank 10 A Blank 20 Blank Blank 30 F Non-Blank 40 A Non-Blank 30 Blank Non-Blank 20 Eg: 20 + 30 + 20 = 70 Thanks in advance, bkt |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Please help w/Sumproduct!
bkt wrote:
Hi, Looking for a Sumproduct formula that will add the quantities in Col. C that have (A in Col. A & "whatever" in Col. B) + Only those Quantities in Col. C that have (Blanks in Col. A & Non-Blanks in Col. B) Col. A Col. B Col. C F Blank 10 A Blank 20 Blank Blank 30 F Non-Blank 40 A Non-Blank 30 Blank Non-Blank 20 Eg: 20 + 30 + 20 = 70 Thanks in advance, bkt Hi bkt, try this: =SUMPRODUCT((A1:A6="A")*C1:C6)+SUMPRODUCT((A1:A6=" Blank")*(B1:B6="Non-Blank")*C1:C6) this work if you have the words "Blank" and "Non-Blank" (without quotes) in column A and B, but if you mean empty and not empty cells, you have to use this one: =SUMPRODUCT((A1:A6="A")*C1:C6)+SUMPRODUCT((A1:A6=" ")*(B1:B6<"")*C1:C6) -- (I'm not sure of names of menus, options and commands, because translating from the Italian version of Excel...) Hope I helped you. Thanks in advance for your feedback. Ciao Franz Verga from Italy |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Please help w/Sumproduct!
Hi again
I guess Franz Verga's 2nd formula will work for you =SUMPRODUCT((A1:A6="A")*C1:C6)+SUMPRODUCT((A1:A6=" ")*(B1:B6<"")*C1:C6) Thanks, Shail bkt wrote: Hi, Looking for a Sumproduct formula that will add the quantities in Col. C that have (A in Col. A & "whatever" in Col. B) + Only those Quantities in Col. C that have (Blanks in Col. A & Non-Blanks in Col. B) Col. A Col. B Col. C F Blank 10 A Blank 20 Blank Blank 30 F Non-Blank 40 A Non-Blank 30 Blank Non-Blank 20 Eg: 20 + 30 + 20 = 70 Thanks in advance, bkt |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Compare row contents w/Sumproduct or Array formula? | Excel Worksheet Functions |