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
5,"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
