Re counting

P

pano

Hi, wonder if you can help

In A1 I have a number in b1 I have two letter. All going downwards

A1 B1

5 AA
3 AB
2 AA
empty empty
2 AB

I would like to be able to count in C1 the total of AA (7) and the
total of AB(5) bearing in mind that some cells will be empty.


Thanks for the help
Regards Stephen
 
F

FSt1

hi,
one way....
=countif(A1:A5000,"AA")
I think I would use a sperate formula of the AB part.

Regards
FSt1
 
E

Epinn

If I understand you correctly ......

In C1: =SUMPRODUCT((B1:B5="AA")+(B1:B5="AB"),A1:A5)

This formula returns 12 which is the sum of 7 (for AA) and 5 (for AB).

For Excel 2003, SUMPRODUCT does not work on columns i.e. A:A or B:B.

If you want to see 7 in one cell and then 5 in another, use SUMIF.

Epinn


Hi, wonder if you can help

In A1 I have a number in b1 I have two letter. All going downwards

A1 B1

5 AA
3 AB
2 AA
empty empty
2 AB

I would like to be able to count in C1 the total of AA (7) and the
total of AB(5) bearing in mind that some cells will be empty.


Thanks for the help
Regards Stephen
 
E

Epinn

For those who don't like SUMPRODUCT, try this

=SUMIF(B1:B5,"AA",A1:A5)+SUMIF(B1:B5,"AB",A1:A5)

This may be more efficient for a large range.

Epinn

If I understand you correctly ......

In C1: =SUMPRODUCT((B1:B5="AA")+(B1:B5="AB"),A1:A5)

This formula returns 12 which is the sum of 7 (for AA) and 5 (for AB).

For Excel 2003, SUMPRODUCT does not work on columns i.e. A:A or B:B.

If you want to see 7 in one cell and then 5 in another, use SUMIF.

Epinn


Hi, wonder if you can help

In A1 I have a number in b1 I have two letter. All going downwards

A1 B1

5 AA
3 AB
2 AA
empty empty
2 AB

I would like to be able to count in C1 the total of AA (7) and the
total of AB(5) bearing in mind that some cells will be empty.


Thanks for the help
Regards Stephen
 
R

Ragdyer

Also:

=SUM(SUMIF(B1:B5,{"AA","AB"},A1:A5))

--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
For those who don't like SUMPRODUCT, try this

=SUMIF(B1:B5,"AA",A1:A5)+SUMIF(B1:B5,"AB",A1:A5)

This may be more efficient for a large range.

Epinn

If I understand you correctly ......

In C1: =SUMPRODUCT((B1:B5="AA")+(B1:B5="AB"),A1:A5)

This formula returns 12 which is the sum of 7 (for AA) and 5 (for AB).

For Excel 2003, SUMPRODUCT does not work on columns i.e. A:A or B:B.

If you want to see 7 in one cell and then 5 in another, use SUMIF.

Epinn


Hi, wonder if you can help

In A1 I have a number in b1 I have two letter. All going downwards

A1 B1

5 AA
3 AB
2 AA
empty empty
2 AB

I would like to be able to count in C1 the total of AA (7) and the
total of AB(5) bearing in mind that some cells will be empty.


Thanks for the help
Regards Stephen
 
E

Epinn

Thanks Ricky for reminding me. Wonder if we can drop SUM in version 2007.

Haven't seen you around much lately. Hope all is well.

Epinn

Also:

=SUM(SUMIF(B1:B5,{"AA","AB"},A1:A5))

--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
For those who don't like SUMPRODUCT, try this

=SUMIF(B1:B5,"AA",A1:A5)+SUMIF(B1:B5,"AB",A1:A5)

This may be more efficient for a large range.

Epinn

If I understand you correctly ......

In C1: =SUMPRODUCT((B1:B5="AA")+(B1:B5="AB"),A1:A5)

This formula returns 12 which is the sum of 7 (for AA) and 5 (for AB).

For Excel 2003, SUMPRODUCT does not work on columns i.e. A:A or B:B.

If you want to see 7 in one cell and then 5 in another, use SUMIF.

Epinn


Hi, wonder if you can help

In A1 I have a number in b1 I have two letter. All going downwards

A1 B1

5 AA
3 AB
2 AA
empty empty
2 AB

I would like to be able to count in C1 the total of AA (7) and the
total of AB(5) bearing in mind that some cells will be empty.


Thanks for the help
Regards Stephen
 
E

Epinn

I know SUMIFS is available for 2007 but no clue how it works.

Epinn

Thanks Ricky for reminding me. Wonder if we can drop SUM in version 2007.

Haven't seen you around much lately. Hope all is well.

Epinn

Also:

=SUM(SUMIF(B1:B5,{"AA","AB"},A1:A5))

--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
For those who don't like SUMPRODUCT, try this

=SUMIF(B1:B5,"AA",A1:A5)+SUMIF(B1:B5,"AB",A1:A5)

This may be more efficient for a large range.

Epinn

If I understand you correctly ......

In C1: =SUMPRODUCT((B1:B5="AA")+(B1:B5="AB"),A1:A5)

This formula returns 12 which is the sum of 7 (for AA) and 5 (for AB).

For Excel 2003, SUMPRODUCT does not work on columns i.e. A:A or B:B.

If you want to see 7 in one cell and then 5 in another, use SUMIF.

Epinn


Hi, wonder if you can help

In A1 I have a number in b1 I have two letter. All going downwards

A1 B1

5 AA
3 AB
2 AA
empty empty
2 AB

I would like to be able to count in C1 the total of AA (7) and the
total of AB(5) bearing in mind that some cells will be empty.


Thanks for the help
Regards Stephen
 
R

Ragdyer

Earning a living has a slightly higher priority on my time then the NGs.

Not quite retired yet, as are some of the folks around here.<g>
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
Thanks Ricky for reminding me. Wonder if we can drop SUM in version 2007.

Haven't seen you around much lately. Hope all is well.

Epinn

Also:

=SUM(SUMIF(B1:B5,{"AA","AB"},A1:A5))

--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
For those who don't like SUMPRODUCT, try this

=SUMIF(B1:B5,"AA",A1:A5)+SUMIF(B1:B5,"AB",A1:A5)

This may be more efficient for a large range.

Epinn

If I understand you correctly ......

In C1: =SUMPRODUCT((B1:B5="AA")+(B1:B5="AB"),A1:A5)

This formula returns 12 which is the sum of 7 (for AA) and 5 (for AB).

For Excel 2003, SUMPRODUCT does not work on columns i.e. A:A or B:B.

If you want to see 7 in one cell and then 5 in another, use SUMIF.

Epinn


Hi, wonder if you can help

In A1 I have a number in b1 I have two letter. All going downwards

A1 B1

5 AA
3 AB
2 AA
empty empty
2 AB

I would like to be able to count in C1 the total of AA (7) and the
total of AB(5) bearing in mind that some cells will be empty.


Thanks for the help
Regards Stephen
 
R

Roger Govier

Hi Epinn
Wonder if we can drop SUM in version 2007
No, it is alive and well in XL2007 and will remain so

SUMIFS(sum_range, criteria_range1, criteria1, criteria_range2,
criteria2 .)

Note that the order has altered, and you give the range to be Summed
first and not third as with SUMIF.
Then you give the 1st criteria range and then the criteria.
You can follow the criteria with up to 126 further ranges each with its
own criteria (or should that be criterium <g>)

So you could have
=SUMIFS(A1:A5,B1:B5,"AA",C1:C5,"AA",D1:D5,"AA")
which would total A1:A5 where AA occurred in column B and C and D

but for the scenario where you want either "AA" OR "AB" in a column
you would still need to use

=SUM(SUMIF(B1:B5,{"AA","AB"},A1:A5))



--
Regards

Roger Govier


I know SUMIFS is available for 2007 but no clue how it works.

Epinn

Thanks Ricky for reminding me. Wonder if we can drop SUM in version
2007.

Haven't seen you around much lately. Hope all is well.

Epinn

Also:

=SUM(SUMIF(B1:B5,{"AA","AB"},A1:A5))

--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit
!
---------------------------------------------------------------------------
For those who don't like SUMPRODUCT, try this

=SUMIF(B1:B5,"AA",A1:A5)+SUMIF(B1:B5,"AB",A1:A5)

This may be more efficient for a large range.

Epinn

If I understand you correctly ......

In C1: =SUMPRODUCT((B1:B5="AA")+(B1:B5="AB"),A1:A5)

This formula returns 12 which is the sum of 7 (for AA) and 5 (for AB).

For Excel 2003, SUMPRODUCT does not work on columns i.e. A:A or B:B.

If you want to see 7 in one cell and then 5 in another, use SUMIF.

Epinn


Hi, wonder if you can help

In A1 I have a number in b1 I have two letter. All going downwards

A1 B1

5 AA
3 AB
2 AA
empty empty
2 AB

I would like to be able to count in C1 the total of AA (7) and the
total of AB(5) bearing in mind that some cells will be empty.


Thanks for the help
Regards Stephen
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top