D
diane
Can you use COUNTIF on multiple ranges of cells? eg
c12:d17 g77:G99 d55:d88 and so on
Thanks in advance
Diane
c12:d17 g77:G99 d55:d88 and so on
Thanks in advance
Diane
F
Frank Kabel
Hi
I would combine multiple COUNTIFs
I would combine multiple COUNTIFs
D
diane
how would I do that? with an OR statement?
F
Frank Kabel
Hi
just add them: Something like
=COUNTIF(A1:A10,"value")+COUNTIF(D5
100,"value")+COUNTIF
(X1:X20,"Value")
just add them: Something like
=COUNTIF(A1:A10,"value")+COUNTIF(D5
100,"value")+COUNTIF(X1:X20,"Value")
D
diane
Yes, that looks like the simplest way of doing it - thanks
a lot
a lot
D
Don Guillett
AFAIK, no but maybe you could use c12:d88???
D
diane
There's a reason that you can't do it that way but thanks
anyway I'll use that one in future - what does AFAIK
mean???
anyway I'll use that one in future - what does AFAIK
mean???
F
Frank Kabel
Hi
AFAIK: As far as I know
AFAIK: As far as I know
D
diane
clever!!
-----Original Message-----
Hi
AFAIK: As far as I know
--
Regards
Frank Kabel
Frankfurt, Germany
.
P
Peo Sjoblom
There are workarounds if the criteria is numeric but if you just have 3
different ranges you can
just use
=countif+countif+countif
different ranges you can
just use
=countif+countif+countif
B
bkube01
What about the case where I have multiple criteria? Adding countif
doesn't seem to work. :confused
doesn't seem to work. :confused
D
Don Guillett
You may need a combination of countif and sumproduct. Details?
B
bkube01
Column A has a "Level", with values of 0, 1, 2, 3, 4, 9.
Column B has a "team", with values of Cardinals, Tigers, Diamondbacks
etc.
Column C has "at bats", with values, of 1, 2, 3, 4, etc.
I want to Sum all of the at bats for the Cardinals with level 0.
Thanks in advance
Column B has a "team", with values of Cardinals, Tigers, Diamondbacks
etc.
Column C has "at bats", with values, of 1, 2, 3, 4, etc.
I want to Sum all of the at bats for the Cardinals with level 0.
Thanks in advance
D
Domenic
bkube01 said:
Hi,
try,
=SUMPRODUCT((A2:A7=0)*(B2:B7="Cardinals")*(C2:C7))
Hope this helps!
R
RagDyer
In A1:C1, enter
Level, Team, AtBats
Start data list in A3:C100
In A2 enter the "level" you wish to lookup,
In B2 enter the "team" you wish to lookup,
In C2, enter this formula:
=SUMPRODUCT((A3:A100=A2)*(B3:B100=B2)*C3:C100)
--
HTH,
RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================
Column A has a "Level", with values of 0, 1, 2, 3, 4, 9.
Column B has a "team", with values of Cardinals, Tigers, Diamondbacks,
etc.
Column C has "at bats", with values, of 1, 2, 3, 4, etc.
I want to Sum all of the at bats for the Cardinals with level 0.
Thanks in advance!
Level, Team, AtBats
Start data list in A3:C100
In A2 enter the "level" you wish to lookup,
In B2 enter the "team" you wish to lookup,
In C2, enter this formula:
=SUMPRODUCT((A3:A100=A2)*(B3:B100=B2)*C3:C100)
--
HTH,
RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================
Column A has a "Level", with values of 0, 1, 2, 3, 4, 9.
Column B has a "team", with values of Cardinals, Tigers, Diamondbacks,
etc.
Column C has "at bats", with values, of 1, 2, 3, 4, etc.
I want to Sum all of the at bats for the Cardinals with level 0.
Thanks in advance!
B
bkube01
Thanks. This appears to work. What function does the asterisk perform
T
trwagner1
Here's a similar question.
I imported a table into Microsoft Excel so I can do bar graphs.
I created a second worksheet where I could create a sheet o
statistics. The statistics will come from the instance of severa
conditions of values from "Sheet1".
First task I tried, worked well. I just wanted to "count" th
instances of someone's initals for each row in sheet1.
=COUNTIF(SHEET1!H2:H527,B1)
i.e., row 1 has all the individuals initials placed.
Now, I want to do a count of the rows which contain BOTH the initial
of the user and a second value.
Example, I want to count all the rows that have the instance of th
initials "BB" (entry in B1). The next statistic I want to gather i
both the initials equal to "BB" and a value in another column of
number or character...both conditions have to be met to count th
instance.
From this sheet of statistics, I will then create bargraphs.
Example of my statistical worksheet:
Calls Made
AR BB CRW LW TRW WY
3 2 4 1 1 5
Example of my Sheet1:
Date Minutes Initials Extension
4/2 31 BB 4156
4/2 2 LW 4132
4/3 11 TRW 4002
4/3 1 WY 4156
4/3 3 WY 4156
4/3 14 CRW 4101
4/3 2 WY 4156
etc., etc., etc.
Example, let's say I want to a count where both the row contained "WY
AND the extension "4156"
In the example, the instance is 3 I want to show that WY called 4156
times.
How can I do that?
Here's what I want the data to look like:
Internal Calls Made
AR BB CRW LW TRW WY
4101 0 0 1 0 0 0
4002 0 0 0 0 0 0
4156 0 0 0 0 0 3
Thanks
Te
I imported a table into Microsoft Excel so I can do bar graphs.
I created a second worksheet where I could create a sheet o
statistics. The statistics will come from the instance of severa
conditions of values from "Sheet1".
First task I tried, worked well. I just wanted to "count" th
instances of someone's initals for each row in sheet1.
=COUNTIF(SHEET1!H2:H527,B1)
i.e., row 1 has all the individuals initials placed.
Now, I want to do a count of the rows which contain BOTH the initial
of the user and a second value.
Example, I want to count all the rows that have the instance of th
initials "BB" (entry in B1). The next statistic I want to gather i
both the initials equal to "BB" and a value in another column of
number or character...both conditions have to be met to count th
instance.
From this sheet of statistics, I will then create bargraphs.
Example of my statistical worksheet:
Calls Made
AR BB CRW LW TRW WY
3 2 4 1 1 5
Example of my Sheet1:
Date Minutes Initials Extension
4/2 31 BB 4156
4/2 2 LW 4132
4/3 11 TRW 4002
4/3 1 WY 4156
4/3 3 WY 4156
4/3 14 CRW 4101
4/3 2 WY 4156
etc., etc., etc.
Example, let's say I want to a count where both the row contained "WY
AND the extension "4156"
In the example, the instance is 3 I want to show that WY called 4156
times.
How can I do that?
Here's what I want the data to look like:
Internal Calls Made
AR BB CRW LW TRW WY
4101 0 0 1 0 0 0
4002 0 0 0 0 0 0
4156 0 0 0 0 0 3
Thanks
Te
T
trwagner1
Here's a similar question.
I imported a table into Microsoft Excel so I can do bar graphs.
I created a second worksheet where I could create a sheet o
statistics. The statistics will come from the instance of severa
conditions of values from "Sheet1".
First task I tried, worked well. I just wanted to "count" th
instances of someone's initals for each row in sheet1.
=COUNTIF(SHEET1!H2:H527,B1)
i.e., row 1 has all the individuals initials placed.
Now, I want to do a count of the rows which contain BOTH the initial
of the user and a second value.
Example, I want to count all the rows that have the instance of th
initials "BB" (entry in B1). The next statistic I want to gather i
both the initials equal to "BB" and a value in another column of
number or character...both conditions have to be met to count th
instance.
From this sheet of statistics, I will then create bargraphs.
Example of my statistical worksheet:
Calls Made
AR BB CRW LW TRW WY
3 2 4 1 1 5
Example of my Sheet1:
Date Minutes Initials Extension
4/2 31 BB 4156
4/2 2 LW 4132
4/3 11 TRW 4002
4/3 1 WY 4156
4/3 3 WY 4156
4/3 14 CRW 4101
4/3 2 WY 4156
etc., etc., etc.
Example, let's say I want to a count where both the row contained "WY
AND the extension "4156"
In the example, the instance is 3 I want to show that WY called 4156
times.
How can I do that?
Here's what I want the data to look like:
Internal Calls Made
AR BB CRW LW TRW WY
4101 0 0 1 0 0 0
4002 0 0 0 0 0 0
4156 0 0 0 0 0 3
Thanks
Te
I imported a table into Microsoft Excel so I can do bar graphs.
I created a second worksheet where I could create a sheet o
statistics. The statistics will come from the instance of severa
conditions of values from "Sheet1".
First task I tried, worked well. I just wanted to "count" th
instances of someone's initals for each row in sheet1.
=COUNTIF(SHEET1!H2:H527,B1)
i.e., row 1 has all the individuals initials placed.
Now, I want to do a count of the rows which contain BOTH the initial
of the user and a second value.
Example, I want to count all the rows that have the instance of th
initials "BB" (entry in B1). The next statistic I want to gather i
both the initials equal to "BB" and a value in another column of
number or character...both conditions have to be met to count th
instance.
From this sheet of statistics, I will then create bargraphs.
Example of my statistical worksheet:
Calls Made
AR BB CRW LW TRW WY
3 2 4 1 1 5
Example of my Sheet1:
Date Minutes Initials Extension
4/2 31 BB 4156
4/2 2 LW 4132
4/3 11 TRW 4002
4/3 1 WY 4156
4/3 3 WY 4156
4/3 14 CRW 4101
4/3 2 WY 4156
etc., etc., etc.
Example, let's say I want to a count where both the row contained "WY
AND the extension "4156"
In the example, the instance is 3 I want to show that WY called 4156
times.
How can I do that?
Here's what I want the data to look like:
Internal Calls Made
AR BB CRW LW TRW WY
4101 0 0 1 0 0 0
4002 0 0 0 0 0 0
4156 0 0 0 0 0 3
Thanks
Te
T
theillknight
I've tried the above formulas:
In A1:C1, enter
Level, Team, AtBats
Start data list in A3:C100
In A2 enter the "level" you wish to lookup,
In B2 enter the "team" you wish to lookup,
In C2, enter this formula:
=SUMPRODUCT((A3:A100=A2)*(B3:B100=B2)*C3:C100)
for my own spreadsheet, but I get a #NUM or #VALUE. I think th
problem is that there are rows of nonsensical information that are no
easy to filter out. Is there anyway around this using formulas and no
modifying the cells? (for example, inbetween the the chart above ther
may be a row of just ----------------'s.
In A1:C1, enter
Level, Team, AtBats
Start data list in A3:C100
In A2 enter the "level" you wish to lookup,
In B2 enter the "team" you wish to lookup,
In C2, enter this formula:
=SUMPRODUCT((A3:A100=A2)*(B3:B100=B2)*C3:C100)
for my own spreadsheet, but I get a #NUM or #VALUE. I think th
problem is that there are rows of nonsensical information that are no
easy to filter out. Is there anyway around this using formulas and no
modifying the cells? (for example, inbetween the the chart above ther
may be a row of just ----------------'s.
N
NeilCollins
trwagner
for a problem like this you will need to use an array formula
ie. if you have your data in columns A to D
put the initials you want to look for in cell F1, the Extension in cel
G1, and then enter following formula in cell H1:
=SUM(IF($C$2:$C$65536=F1,IF($D2:$D65536=G1,1)))
substituting the 2 and the 65536 for the first and last data containin
rows.
when this is entered, but you are still actually editing the formula
press SHIFT+CTRL+ENTER at the same time. This will turn it into a
array formula, and should put curly brackets around the formula {}. Yo
cannot put these in manually. If it doesn't appear to work, make sur
that you are actually 'editing' the cell containing the formula - pres
F2 when it is selected and then press SHIFT+CTRL+ENTER again.
nb. the formula shown will give you a number of occurrences. if yo
want to count the number of minutes for thes initials at thi
extension, use the following formula
=SUM(IF($C$2:$C$65536=F1,IF($D2:$D65536=G1,$B$2:$B$65536)))
again, taking care to make sure you turn it into an array formula. hop
that helps.
nei
for a problem like this you will need to use an array formula
ie. if you have your data in columns A to D
put the initials you want to look for in cell F1, the Extension in cel
G1, and then enter following formula in cell H1:
=SUM(IF($C$2:$C$65536=F1,IF($D2:$D65536=G1,1)))
substituting the 2 and the 65536 for the first and last data containin
rows.
when this is entered, but you are still actually editing the formula
press SHIFT+CTRL+ENTER at the same time. This will turn it into a
array formula, and should put curly brackets around the formula {}. Yo
cannot put these in manually. If it doesn't appear to work, make sur
that you are actually 'editing' the cell containing the formula - pres
F2 when it is selected and then press SHIFT+CTRL+ENTER again.
nb. the formula shown will give you a number of occurrences. if yo
want to count the number of minutes for thes initials at thi
extension, use the following formula
=SUM(IF($C$2:$C$65536=F1,IF($D2:$D65536=G1,$B$2:$B$65536)))
again, taking care to make sure you turn it into an array formula. hop
that helps.
nei