Adding 2 colums of data
 

I would like to count 2 colums of data for a sum

Ex: =countif((Sheet3!C5:C35="name")*(Sheet3!E5:E35="O* *"))

Please advise.

Adding 2 colums of data
 

Try
=SUMPRODUCT((Sheet3!C5:C35="name")*(LEFT(Sheet3!E5 :E35,1)="O"))

If this post helps click Yes
---------------
Jacob Skaria


"Mamagga" wrote:

I would like to count 2 colums of data for a sum

Ex: =countif((Sheet3!C5:C35="name")*(Sheet3!E5:E35="O* *"))

Please advise.

Adding 2 colums of data
 

=SUMPRODUCT(--(Sheet3!C5:C35="name"), --(Sheet3!E5:E35="O"))
will count (not sum) all row having 'name' in column E and "O" in column E

What your meaning of "O**" ?
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email


"Mamagga" wrote in message
...
I would like to count 2 colums of data for a sum

Ex: =countif((Sheet3!C5:C35="name")*(Sheet3!E5:E35="O* *"))

Please advise.

Adding 2 colums of data
 

Hi Jacob,
I used the formula below and a was somewhat successful - it added but not
the right total. Maybe if you explained the formula (ie. what is "left" for
and the ,1 after the array?) I could figure it out.
Thaks

"Jacob Skaria" wrote:

Try
=SUMPRODUCT((Sheet3!C5:C35="name")*(LEFT(Sheet3!E5 :E35,1)="O"))

If this post helps click Yes
---------------
Jacob Skaria


"Mamagga" wrote:

I would like to count 2 colums of data for a sum

Ex: =countif((Sheet3!C5:C35="name")*(Sheet3!E5:E35="O* *"))

Please advise.

Adding 2 colums of data
 

Hi Bernard,

I tried your formula and it is not adding up. The "O" is a letter. What I
would like the formula to do is count if the "name" is in column C and the
letter "O" is in column E add.

"Bernard Liengme" wrote:

=SUMPRODUCT(--(Sheet3!C5:C35="name"), --(Sheet3!E5:E35="O"))
will count (not sum) all row having 'name' in column E and "O" in column E

What your meaning of "O**" ?
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email


"Mamagga" wrote in message
...
I would like to count 2 colums of data for a sum

Ex: =countif((Sheet3!C5:C35="name")*(Sheet3!E5:E35="O* *"))

Please advise.

Adding 2 colums of data
 

I'm betting that Bernard wanted to know what those ** represented?

Did you mean for them to be wildcards (and why did you use 2 of them)?
=SUMPRODUCT(--(Sheet3!C5:C35="name"), --left((Sheet3!E5:E35,1)="O"))
(=left(a1,1) returns the first/leftmost character in A1.)

Or are they the characters that appear in the cell?
=SUMPRODUCT(--(Sheet3!C5:C35="name"), --(Sheet3!E5:E35="O**"))

Mamagga wrote:

Hi Bernard,

I tried your formula and it is not adding up. The "O" is a letter. What I
would like the formula to do is count if the "name" is in column C and the
letter "O" is in column E add.

"Bernard Liengme" wrote:

=SUMPRODUCT(--(Sheet3!C5:C35="name"), --(Sheet3!E5:E35="O"))
will count (not sum) all row having 'name' in column E and "O" in column E

What your meaning of "O**" ?
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email


"Mamagga" wrote in message
...
I would like to count 2 colums of data for a sum

Ex: =countif((Sheet3!C5:C35="name")*(Sheet3!E5:E35="O* *"))

Please advise.




--

Dave Peterson

Adding 2 colums of data
 

=SUMPRODUCT((Sheet3!C5:C35="name")*(LEFT(Sheet3!E5 :E35,1)="O"))
Count the number of rows with colC = "name" and the text in Column E starts
with O

--
If this post helps click Yes
---------------
Jacob Skaria


"Mamagga" wrote:

Hi Jacob,
I used the formula below and a was somewhat successful - it added but not
the right total. Maybe if you explained the formula (ie. what is "left" for
and the ,1 after the array?) I could figure it out.
Thaks

"Jacob Skaria" wrote:

Try
=SUMPRODUCT((Sheet3!C5:C35="name")*(LEFT(Sheet3!E5 :E35,1)="O"))

If this post helps click Yes
---------------
Jacob Skaria


"Mamagga" wrote:

I would like to count 2 colums of data for a sum

Ex: =countif((Sheet3!C5:C35="name")*(Sheet3!E5:E35="O* *"))

Please advise.

Adding 2 colums of data
 

Hi Dave the ** are just characters in the cell not wild cards.

"Dave Peterson" wrote:

I'm betting that Bernard wanted to know what those ** represented?

Did you mean for them to be wildcards (and why did you use 2 of them)?
=SUMPRODUCT(--(Sheet3!C5:C35="name"), --left((Sheet3!E5:E35,1)="O"))
(=left(a1,1) returns the first/leftmost character in A1.)

Or are they the characters that appear in the cell?
=SUMPRODUCT(--(Sheet3!C5:C35="name"), --(Sheet3!E5:E35="O**"))

Mamagga wrote:

Hi Bernard,

I tried your formula and it is not adding up. The "O" is a letter. What I
would like the formula to do is count if the "name" is in column C and the
letter "O" is in column E add.

"Bernard Liengme" wrote:

=SUMPRODUCT(--(Sheet3!C5:C35="name"), --(Sheet3!E5:E35="O"))
will count (not sum) all row having 'name' in column E and "O" in column E

What your meaning of "O**" ?
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email


"Mamagga" wrote in message
...
I would like to count 2 colums of data for a sum

Ex: =countif((Sheet3!C5:C35="name")*(Sheet3!E5:E35="O* *"))

Please advise.




--

Dave Peterson

Adding 2 colums of data
 

So did that suggestion work?

Or how did it fail?

Mamagga wrote:

Hi Dave the ** are just characters in the cell not wild cards.

"Dave Peterson" wrote:

I'm betting that Bernard wanted to know what those ** represented?

Did you mean for them to be wildcards (and why did you use 2 of them)?
=SUMPRODUCT(--(Sheet3!C5:C35="name"), --left((Sheet3!E5:E35,1)="O"))
(=left(a1,1) returns the first/leftmost character in A1.)

Or are they the characters that appear in the cell?
=SUMPRODUCT(--(Sheet3!C5:C35="name"), --(Sheet3!E5:E35="O**"))

Mamagga wrote:

Hi Bernard,

I tried your formula and it is not adding up. The "O" is a letter. What I
would like the formula to do is count if the "name" is in column C and the
letter "O" is in column E add.

"Bernard Liengme" wrote:

=SUMPRODUCT(--(Sheet3!C5:C35="name"), --(Sheet3!E5:E35="O"))
will count (not sum) all row having 'name' in column E and "O" in column E

What your meaning of "O**" ?
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email


"Mamagga" wrote in message
...
I would like to count 2 colums of data for a sum

Ex: =countif((Sheet3!C5:C35="name")*(Sheet3!E5:E35="O* *"))

Please advise.




--

Dave Peterson


--

Dave Peterson