OK, you haven't said which interpretation of your post is correct. So....
Here's a small sample file that demonstrates my interpretation. I used your
sample data and replaced "n" with random numbers.
Sample file:
xSumproduct.xls 14kb
http://cjoint.com/?gvihcq6juw
As you'll see the formula does work and it does return the correct results.
>There are no commas separating the arguments.
Don't get "obsessed" over commas!
--
Biff
Microsoft Excel MVP
"Setts" <(E-Mail Removed)> wrote in message
news:7F988263-B935-4CA0-9A26-(E-Mail Removed)...
> Biff: Sorry, no go. I tried your SUMPRODUCT various ways and none
> worked.
> Always ### Value ###. Is the suggested function have a reference to only
> one array? There are no commas separating the arguments. Setts
>
> "T. Valko" wrote:
>
>> > =SUMPRODUCT((A1:A100="aaa")*(B1:B100<>"")*(C1:E100="n"))
>>
>> That formula is counting the literal character "n". Is that what you
>> wanted?
>>
>> My interpretation of your post is "n" represents a number and you want
>> the
>> SUM.
>>
>> >aaa in Column A and [n]ull (blanks) in Column B.
>>
>> =SUMPRODUCT((A1:A8="aaa")*(B1:B8="")*C1:E8)
>>
>> >specific criteria in Column A and anything in Column B
>> >aaa and yyy with the aaa and www.
>>
>> =SUMPRODUCT((A1:A8="aaa")*(B1:B8<>"")*C1:E8)
>>
>> --
>> Biff
>> Microsoft Excel MVP
>>
>>
>> "Setts" <(E-Mail Removed)> wrote in message
>> news:3092D19D-842C-48CE-86D4-(E-Mail Removed)...
>> > Would you kindly explain how this works? Since there are no commas it
>> > seems
>> > you specified one argument and that argument is an expression not an
>> > array
>> > reference. I have been trying various ways and get nothing that seems
>> > usuable. Please explain how this adds up only those numbers that are
>> > in
>> > rows
>> > that match data in other columns in those rows, including arguments
>> > that
>> > are
>> > expressions (e.g. <>"") and others strings ("aaa"). Sorry for being so
>> > dense. Setts
>> >
>> > "Teethless mama" wrote:
>> >
>> >> =SUMPRODUCT((A1:A100="aaa")*(B1:B100<>"")*(C1:E100="n"))
>> >>
>> >>
>> >>
>> >> "Setts" wrote:
>> >>
>> >> > I asked this before but my explanation was labored and the answer
>> >> > didn't
>> >> > address my needs. Here is a fuller, and I hope, clearer
>> >> > explanation.
>> >> > How do
>> >> > add the figures in one or more columns based on criteria in more
>> >> > than
>> >> > one
>> >> > column? For example assume the following worksheet:
>> >> >
>> >> > A B C D E
>> >> >
>> >> > 1 aaa n n n
>> >> > 2 bbb zzz n n n
>> >> > 3 aaa yyy n n n
>> >> > 4 ccc xxx n n n
>> >> > 5 aaa www n n n
>> >> > 6 bbb n n n
>> >> > 7 bbb zzz n n n
>> >> > 8 ccc n n n
>> >> >
>> >> > The real worksheet has hundreds of rows. I would like to add up the
>> >> > n's in
>> >> > the rows that have a specific criteria, e.g. aaa in Column A and
>> >> > bull
>> >> > (blanks) in Column B. I would also like to add up the n's in the
>> >> > rows
>> >> > that
>> >> > have a specific criteria in Column A and anything in Column B (not
>> >> > null
>> >> > or
>> >> > blank), e.g. aaa and yyy with the aaa and www. There are too many
>> >> > different
>> >> > strings in Column B to use specific criteria. Any help would br
>> >> > greatly
>> >> > appreciated. Setts
>>
>>
>>
Similar Threads
