using sumif nesting

[Bob]

In a column down I have text ie: xx,aa,oo,pp-text,pp-
different text,rr, . On the same sheet I have another
column with text ddd, ppp, vvv. In another column I have
numbers 6,7,4 etc),

I want to total only the rows that contain PP- and vvv.

 

[JE McGimpsey]

one way:

=SUMPRODUCT(--(A1:A1000="PP-"),--(B1:B1000="vvv"),C1:C1000)

 

[Mark Graesser]

Bob
You can use SUMPRODUCT for this

=SUMPRODUCT((A1:A100="PP")*(B1:B100="vvv")*(C1:C100)

I have included a write-up I have been putting together on this function

Good Luck
Mark Graesse
(e-mail address removed)
Boston M

SUMPRODUCT

The SUMPRODUCT function will create an array of values for each argument. The corresponding components of each array are then multiplied, and these products are added

The arrays must be of the same size, and in the same direction (vertical or horizontal). However, they do not have to be level. One can use an array of A1:A5 in one argument and an array of B11:B15 in another argument. Arrays of conflicting size will produce a #N/A error

Also, the arrays must be of a definite size. Full column references (A:A) are not valid and will return a #NUM error

The use of a single multiplier is also acceptable. SUMPRODUCT( (A1:A5) * 5

For conditional arguments the return is a 0 if false and a 1 if tru

Non-conditional arguments, values used directly from the spreadsheet, must be numeric. Text arguments will return a #VALUE error

SAMPLE

With the following table in A1:C

A D
A E
A F
B D
B E
B F
C D
C E
C F

And the formula

=SUMPRODUCT( (A1:A9=â€Bâ€) * (B1:B9=â€Eâ€) * (C1:C9)

The resultant arrays produced are

(0,0,0,1,1,1,0,0,0) * (0,1,0,0,1,0,0,1,0,) * (1,2,3,4,5,6,7,8,9

The products of the corresponding components then produce

(0*0*1) + (0*1*2) + (0*0*3) + (1*0*4) + (1*1*5) + … =
(0 + 0 + 0 + 0 + 5 + 0 + 0 + 0 + 0

And the final sum of these products is


----- Bob wrote: ----

In a column down I have text ie: xx,aa,oo,pp-text,pp
different text,rr, . On the same sheet I have another
column with text ddd, ppp, vvv. In another column I have
numbers 6,7,4 etc)

I want to total only the rows that contain PP- and vvv

 

[Bob]

The text after PP- is variable, however, I need it to add
the rows with pp regardless of the text after the dash.
something with a wildcard like "pp-*"

These formulas will not work with the variable text after
the pp-.

Is there something that will work with a wildcard?

 

[Mark Graesser]

Hi Bob
You could just compare the first two characters in the first column

=SUMPRODUCT((LEFT(A1:A100,2)="PP")*(B1:B100="vvv")*(C1:C100)

Regards
Mark Graesse
(e-mail address removed)
Boston M

----- Bob wrote: ----

The text after PP- is variable, however, I need it to add
the rows with pp regardless of the text after the dash
something with a wildcard like "pp-*

These formulas will not work with the variable text after
the pp-

Is there something that will work with a wildcard

 

[Bob Phillips]

Bob,

Is this what you want

=SUMPRODUCT((LEFT(A1:A100,3)="PP-")*(B1:B100="vvv")*(C1:C100))

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Bob Phillips]

Mark,

I agree, and I did notice that you did that in your original post. I used
PP- as it was more aligned to the OP, but I would do as you did.

Bob

 

[Guest]

Ahhhh...

works beautifully. Thanks.

 

[Mark Graesser]

Hi Bob
I just rechecked the original request. The only reason I used "pp" instead of "pp-" is because I wasn't paying attention when I read the post. I'm so good that even my mistakes are good suggestions

Oh yeah, I'm also extremely modest. ;-

Regards
Mark Graesse
(e-mail address removed)
Boston M

----- Bob Phillips wrote: ----

Mark

I agree, and I did notice that you did that in your original post. I use
PP- as it was more aligned to the OP, but I would do as you did

Bo