|
Favourites: 1
sharing some advanced Excel tricks ...
Posted by
zork
Dec 6 '11, 10:01
|
... often to get the information you want, you find yourself calculating intermediate data in a new column and then doing a count or sum of data in that temporary column. Here are some tips to avoid doing that and getting the required total in one step ...
cool Excel tip #1:
=COUNTIF(B:B,"d*")
gives you the number of cells in column B that *start* with d or D
cool Excel tip #2:
=SUM(IF(LEN(D1:D65536)=6,1))
gives you the number of cells in column D with a text length of 6
This is an array formula so you *have* to hit ctrl-shift-enter when editing the cell for Excel to register it that way.
extreme Excel tip:
=SUM(IF((G2:G2000="yes")*(A2:A2000=2006),C2:C2000))
gives the sum the numbers stored in column C but only including rows when column G is "yes" and 2006 is in column A. The multiple condition is defined with (condition1)*(condition2).
This also is an array formula and must be entered with ctrl-shift-enter
|
Responses:
|