| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How to separate x y data in 1 column to 2 colums of x and y data? | Excel Discussion (Misc queries) | |||
| Adding colums | Excel Worksheet Functions | |||
| Global Macro Adding rows or colums to many Excel files at the same | Excel Discussion (Misc queries) | |||
| How can I hide data in colums. | Excel Discussion (Misc queries) | |||
| same data colums | Excel Worksheet Functions | |||
Linear Mode
