Totals f(x) text values in multiple columns and cells
 

Text data is listed in column A, B, and C. Numerical data in column D. I
need to total the values of the numbers in column D for those instances in
which column A has a particular text value, column B has a particular text
value, AND column C has any one of three text values.

Thanks in advance . . .

Totals f(x) text values in multiple columns and cells
 

Try something like this:

=SUMPRODUCT(--(A1:A10="A"),--(B1:B10="B"),--((C1:C10="A")+(C1:C10="B")+(C1:C10="C")),(D1:D10))

HTH,
Elkar


"jkl" wrote:

Text data is listed in column A, B, and C. Numerical data in column D. I
need to total the values of the numbers in column D for those instances in
which column A has a particular text value, column B has a particular text
value, AND column C has any one of three text values.

Thanks in advance . . .

Totals f(x) text values in multiple columns and cells
 

=sumproduct(--(A1:A64000=Criteria A),--(B1:B64000=Criteria
B),--(C1:C64000={criteria c1,Criteria C2,Criteria C3}),D1:D64000)
the "--)" changes the loogical true false to a numeric 1 0
the arrays must be the same size and cannot refer to a complete column A:A
will not work

"jkl" wrote:

Text data is listed in column A, B, and C. Numerical data in column D. I
need to total the values of the numbers in column D for those instances in
which column A has a particular text value, column B has a particular text
value, AND column C has any one of three text values.

Thanks in advance . . .

Totals f(x) text values in multiple columns and cells
 

If your list goes from row 1 through row 9 (change as needed to deal with
your reality) for the values in D associated with particular text in A:
=SUMPRODUCT(--(A1:A9="ColATestPhrase"),(D1:D9))
similarly for column B
=SUMPRODUCT(--(B1:B9="ColBTestPhrase"),(D1:D9))
and we'll keep it simple in the 3-possible values for Column C formula (this
is all one long formula):
=SUMPRODUCT(--(C1:C9="ColCTestPhrase1"),(D1:D9)) +
SUMPRODUCT(--(C1:C9="ColCTestPhrase2"),(D1:D9)) +
SUMPRODUCT(--(C1:C9="ColCTestPhrase3"),(D1:D9))

The SUMPRODUCT basically multiplies all of the individual elements
referenced in it and addes the result of each multiplication together. in
the portion with the --( setup, we are coercing the results of the test (true
or false) to use as a multiplier - it will be 0 for false, 1 for true. No
match = 0*D, Match = 1*D.

In the last one with 3 possible conditions, assuming all 3 test phrases are
unique, only one of the three SUMPRODUCT formulas will return a non-zero
result. If none of the phrases match what's in column C, then 0+0+0 = 0.




"jkl" wrote:

Text data is listed in column A, B, and C. Numerical data in column D. I
need to total the values of the numbers in column D for those instances in
which column A has a particular text value, column B has a particular text
value, AND column C has any one of three text values.

Thanks in advance . . .

Totals f(x) text values in multiple columns and cells
 

go with one of the other suggestions, Something is not working with mine,

"bj" wrote:

=sumproduct(--(A1:A64000=Criteria A),--(B1:B64000=Criteria
B),--(C1:C64000={criteria c1,Criteria C2,Criteria C3}),D1:D64000)
the "--)" changes the loogical true false to a numeric 1 0
the arrays must be the same size and cannot refer to a complete column A:A
will not work

"jkl" wrote:

Text data is listed in column A, B, and C. Numerical data in column D. I
need to total the values of the numbers in column D for those instances in
which column A has a particular text value, column B has a particular text
value, AND column C has any one of three text values.

Thanks in advance . . .

Totals f(x) text values in multiple columns and cells
 

=SUMPRODUCT((A1:A100="A")*(B1:B100="B")*(C1:C100={ "X","Y","Z"})*(D1:D100))

Adjust to suit



"jkl" wrote:

Text data is listed in column A, B, and C. Numerical data in column D. I
need to total the values of the numbers in column D for those instances in
which column A has a particular text value, column B has a particular text
value, AND column C has any one of three text values.

Thanks in advance . . .

Totals f(x) text values in multiple columns and cells
 

Hi Elkar.

I tried using the formula but it didn't work. So I tried to simply focus on
one column of text values (see below). But it still keeps bringing up a
value of 0 . . . when the value should be more than ten million. Not sure
what's amiss. But thanks for giving me a suggestion!

=SUMPRODUCT(--(AI5:AI356="*governing*"),(M5:M356))

Best,

JKL


"Elkar" wrote:

Try something like this:

=SUMPRODUCT(--(A1:A10="A"),--(B1:B10="B"),--((C1:C10="A")+(C1:C10="B")+(C1:C10="C")),(D1:D10))

HTH,
Elkar


"jkl" wrote:

Text data is listed in column A, B, and C. Numerical data in column D. I
need to total the values of the numbers in column D for those instances in
which column A has a particular text value, column B has a particular text
value, AND column C has any one of three text values.

Thanks in advance . . .

Totals f(x) text values in multiple columns and cells
 

Hi.

Yeah, I'm running into problems as well. See my reply to Elkar. I'll keep
at it.

Thanks again!



"bj" wrote:

go with one of the other suggestions, Something is not working with mine,

"bj" wrote:

=sumproduct(--(A1:A64000=Criteria A),--(B1:B64000=Criteria
B),--(C1:C64000={criteria c1,Criteria C2,Criteria C3}),D1:D64000)
the "--)" changes the loogical true false to a numeric 1 0
the arrays must be the same size and cannot refer to a complete column A:A
will not work

"jkl" wrote:

Text data is listed in column A, B, and C. Numerical data in column D. I
need to total the values of the numbers in column D for those instances in
which column A has a particular text value, column B has a particular text
value, AND column C has any one of three text values.

Thanks in advance . . .

Totals f(x) text values in multiple columns and cells
 

Hi.

Thanks tons. I gave it a try but something isn't working. See my reply to
Elkar.

I'll keep at it . . .

Thanks again.

~ JKL

"JLatham" wrote:

If your list goes from row 1 through row 9 (change as needed to deal with
your reality) for the values in D associated with particular text in A:
=SUMPRODUCT(--(A1:A9="ColATestPhrase"),(D1:D9))
similarly for column B
=SUMPRODUCT(--(B1:B9="ColBTestPhrase"),(D1:D9))
and we'll keep it simple in the 3-possible values for Column C formula (this
is all one long formula):
=SUMPRODUCT(--(C1:C9="ColCTestPhrase1"),(D1:D9)) +
SUMPRODUCT(--(C1:C9="ColCTestPhrase2"),(D1:D9)) +
SUMPRODUCT(--(C1:C9="ColCTestPhrase3"),(D1:D9))

The SUMPRODUCT basically multiplies all of the individual elements
referenced in it and addes the result of each multiplication together. in
the portion with the --( setup, we are coercing the results of the test (true
or false) to use as a multiplier - it will be 0 for false, 1 for true. No
match = 0*D, Match = 1*D.

In the last one with 3 possible conditions, assuming all 3 test phrases are
unique, only one of the three SUMPRODUCT formulas will return a non-zero
result. If none of the phrases match what's in column C, then 0+0+0 = 0.




"jkl" wrote:

Text data is listed in column A, B, and C. Numerical data in column D. I
need to total the values of the numbers in column D for those instances in
which column A has a particular text value, column B has a particular text
value, AND column C has any one of three text values.

Thanks in advance . . .

Totals f(x) text values in multiple columns and cells
 

Thanks for weighing in. Alas, I keep getting #VALUE! . . . Not sure why . .
.. I'll keep at it . . .

Thanks again

"Teethless mama" wrote:

=SUMPRODUCT((A1:A100="A")*(B1:B100="B")*(C1:C100={ "X","Y","Z"})*(D1:D100))

Adjust to suit



"jkl" wrote:

Text data is listed in column A, B, and C. Numerical data in column D. I
need to total the values of the numbers in column D for those instances in
which column A has a particular text value, column B has a particular text
value, AND column C has any one of three text values.

Thanks in advance . . .

Totals f(x) text values in multiple columns and cells
 

The SUMPRODUCT function won't recognize wildcard characters (* and ?). Try
something like this instead:

=SUMPRODUCT(--(NOT(ISERROR(SEARCH("governing",AI5:AI356)))),(M5: M356))

Note that the SEARCH function is not case sensitive. You can replace SEARCH
with FIND if you want it to be case sensitive.

HTH,
Elkar

"jkl" wrote:

Hi Elkar.

I tried using the formula but it didn't work. So I tried to simply focus on
one column of text values (see below). But it still keeps bringing up a
value of 0 . . . when the value should be more than ten million. Not sure
what's amiss. But thanks for giving me a suggestion!

=SUMPRODUCT(--(AI5:AI356="*governing*"),(M5:M356))

Best,

JKL


"Elkar" wrote:

Try something like this:

=SUMPRODUCT(--(A1:A10="A"),--(B1:B10="B"),--((C1:C10="A")+(C1:C10="B")+(C1:C10="C")),(D1:D10))

HTH,
Elkar


"jkl" wrote:

Text data is listed in column A, B, and C. Numerical data in column D. I
need to total the values of the numbers in column D for those instances in
which column A has a particular text value, column B has a particular text
value, AND column C has any one of three text values.

Thanks in advance . . .