How to get the sum of B:C by counting the instances that a nameappears in range A

[Sheena Carmel Opulencia]

Hello everyone

Just to give an example

Range A1:A25 shows names (Jay, Al, Sam)

Range B:C are all numbers.

I need to get the sum of B:C depending on the instances that Jay appeared from A:A.

I know I need to use countif for A:A but I don't know how can I sum up all the values from B:C and combine them in one formula.

 

[Norman Jones]

Hello everyone

Just to give an example

Range A1:A25 shows names (Jay, Al, Sam)

Range B:C are all numbers.

I need to get the sum of B:C depending on the instances that Jay appeared from A:A.

I know I need to use countif for A:A but I don't know how can I sum up all the values from B:C and combine them in one formula.

Hi Sheena,

Try:

=SUMPRODUCT((A1:A25="Jay")*(B1:B25))



===
Regards,
Norman

 

[Claus Busch]

Hi Sheena,

Am Mon, 3 Feb 2014 22:34:06 -0800 (PST) schrieb Sheena Carmel Opulencia:

Range A1:A25 shows names (Jay, Al, Sam)

Range B:C are all numbers.

I need to get the sum of B:C depending on the instances that Jay appeared from A:A.

try:
=SUMPRODUCT(--(A1:A25="Jay"),(B1:B25+C1:C25))
or
=SUM(INDEX(B:C,MATCH("Jay",A1:A25,0),))
or
=SUMIF(A1:A25,"Jay",B1:B25)+SUMIF(A1:A25,"Jay",C1:C25)


Regards
Claus B.

 

[Sheena Carmel Opulencia]

Hi Norman,

I got the solution by using SUMIF.

I now have a new concern as I would need to use two different criteria from two different columns to get the sum of two other columns.

Example: I need to find

Range A - Names
Range B - Places

C and D - numbers

I need to find the sum of the values in C and D using "Jay" and "Seattle" from A and B.

 

[Claus Busch]

Hi Sheena,

Am Tue, 4 Feb 2014 01:23:57 -0800 (PST) schrieb Sheena Carmel Opulencia:

Range A - Names
Range B - Places

C and D - numbers

try:
=SUMPRODUCT((A1:A25="Jay")*(B1:B25="Seattle")*((C1:C25)+(D125)))


Regards
Claus B.

 

[Sheena Carmel Opulencia]

It didn't work...

Hi Sheena,



Am Tue, 4 Feb 2014 01:23:57 -0800 (PST) schrieb Sheena Carmel Opulencia:







try:

=SUMPRODUCT((A1:A25="Jay")*(B1:B25="Seattle")*((C1:C25)+(D125)))





Regards

Claus B.

--

Win XP PRof SP2 / Vista Ultimate SP2

Office 2003 SP2 /2007 Ultimate SP2

 

[Norman Jones]

It didn't work...

Hi Sheena,

Claus's suggested formula works for me, as does the following variation:


=SUMPRODUCT((A1:A25="Jay")*(B1:B25="Seattle")*(C1:C25))


===
Regards,
Norman

 

[Norman Jones]

Hi Sheena,

Claus's suggested formula works for me, as does the following variation:


=SUMPRODUCT((A1:A25="Jay")*(B1:B25="Seattle")*(C1:C25))


===
Regards,
Norman

Hi Sheena,

My variation formula was missing an argument!

However, as indicated, Claus's formula works for me - assuming that you
wish to add the contents of columns C and D where your criteria are
satisfied in columns B and C.

If Claus's formula does not provide the expected result, it may be
helpful to explain what you operation you wish to conduct on columns C
and D.



===
Regards,
Norman

 

[Claus Busch]

Hi Sheena,

Am Tue, 4 Feb 2014 01:55:55 -0800 (PST) schrieb Sheena Carmel Opulencia:

It didn't work...

if it does not work you have leading or trailing spaces in your cells
with the names and places. Delete them by using TextToColumns => Fixed
Width => Finish
The ranges into this formula must have the same number of rows.


Regards
Claus B.