Re counting

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

pano - 04 Feb 2007 02:00 GMT

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

Reply to this Message

FSt1 - 04 Feb 2007 02:45 GMT

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

Regards
FSt1

> Hi, wonder if you can help
>
[quoted text clipped - 13 lines]
> Thanks for the help
> Regards Stephen

Reply to this Message

FSt1 - 04 Feb 2007 06:03 GMT

opps.
I misunderstood your post. instead of countif, use sumif.

Regards
FSt1

> hi,
> one way....
[quoted text clipped - 21 lines]
> > Thanks for the help
> > Regards Stephen

Reply to this Message

Epinn - 04 Feb 2007 02:47 GMT

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

Reply to this Message

Epinn - 05 Feb 2007 02:18 GMT

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.

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

"pano" <jenawyn@netspace.net.au> wrote in message news:1170554402.604455.47090@k78g2000cwa.googlegroups.com...
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

Reply to this Message

Ragdyer - 05 Feb 2007 03:35 GMT

Also:

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

Signature

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

"Epinn" <someone@example.com.NO_SPAM> wrote in message
news:OmvIsbASHHA.4076@TK2MSFTNGP05.phx.gbl...
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

Reply to this Message

Epinn - 05 Feb 2007 07:41 GMT

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))

Signature

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

Reply to this Message

Epinn - 05 Feb 2007 07:46 GMT

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

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

"Ragdyer" <RagDyer@cutoutmsn.com> wrote in message news:e2lK#aNSHHA.480@TK2MSFTNGP02.phx.gbl...
Also:

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

Signature

Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Epinn" <someone@example.com.NO_SPAM> wrote in message
news:OFyanvMSHHA.480@TK2MSFTNGP02.phx.gbl...
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

"Epinn" <someone@example.com.NO_SPAM> wrote in message
news:OmvIsbASHHA.4076@TK2MSFTNGP05.phx.gbl...
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

Reply to this Message

Roger Govier - 06 Feb 2007 08:24 GMT

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))

Signature

Regards

Roger Govier

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

Epinn

"Epinn" <someone@example.com.NO_SPAM> wrote in message
news:#wgTBkPSHHA.996@TK2MSFTNGP02.phx.gbl...
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))

Signature

Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit
!
---------------------------------------------------------------------------
"Epinn" <someone@example.com.NO_SPAM> wrote in message
news:OFyanvMSHHA.480@TK2MSFTNGP02.phx.gbl...
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

"Epinn" <someone@example.com.NO_SPAM> wrote in message
news:OmvIsbASHHA.4076@TK2MSFTNGP05.phx.gbl...
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

Reply to this Message

Ragdyer - 06 Feb 2007 02:42 GMT

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>

Signature

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))

Signature

Reply to this Message