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

[Guest]

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 . . .

 

[Guest]

Try something like this:

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

HTH,
Elkar

 

[Guest]

=sumproduct(--(A1:A64000=Criteria A),--(B1:B64000=Criteria
B),--(C1:C64000={criteria c1,Criteria C2,Criteria C3}),D164000)
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

 

[Guest]

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"),(D19))
similarly for column B
=SUMPRODUCT(--(B1:B9="ColBTestPhrase"),(D19))
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"),(D19)) +
SUMPRODUCT(--(C1:C9="ColCTestPhrase2"),(D19)) +
SUMPRODUCT(--(C1:C9="ColCTestPhrase3"),(D19))

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.

 

[Guest]

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

 

[Guest]

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

Adjust to suit

 

[Guest]

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

 

[Guest]

Hi.

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

Thanks again!

 

[Guest]

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

 

[Guest]

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

Thanks again

 

[Guest]

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