Tech2 Touch

Author: Aniruddh

  • Using Excel Multiple condition COUNTIF function

    In my previous post, we have seen how to use single criteria Excel COUNTIF function. That works great  when you need to count a specific range of cells based on one condition.  Some times, there are situation we are required to count range of cells based on 2 or more conditions.  So let us have a quick look on the same.

    Excel Multi condition count function  with AND:

    The And criteria count function counts the cells if all the conditions are met.  The simple example for this is counting a range of cells which falls in between a numerical value. For example, counting cells that contains a value greater 0 and less than or equal to 20. Any cell that has positive value less than or equal to 20 will calculated in the Excel multi count with AND.For using this we need to use COUNTIF excel function or SUMPRODUCT function.

    Syntax of COUNTIFS function:

    =COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2]…)

    Where
    criteria_range1:  Required. The first range in which to evaluate the associated criteria.
    criteria1 :  Required. The criteria in the form of a number, expression, cell reference, or text that define which cells will be counted. For example, criteria can be expressed as 32, “>32”, B4, “apples”, or “32”.
    criteria_range2, criteria2, …    Optional. Additional ranges and their associated criteria. Up to 127 range/criteria pairs are allowed.

    Note: COUNTIFS works only with Excel 2007 and higher versions. For using multiple condition count in earlier version of Excel we need to sumproduct.

    Syntax for using SUMPRODUCT function as multicondition count:

    As we have seen earlier, SUMPRODUCT function can be used to count range of cells based on multiple criteria.
    Example for using Multicondition count in Excel: Below the sample data.

    Counting only the sales of Apple for the month of Jan with value less than 250:
    1. Using countifs function: =COUNTIFS(A2:A13,”Jan”,C2:C13,”Apple”, D2:D13,”2. Using the SUMPRODUCT function: =SUMPRODUCT((A2:A13=”Jan”)*(C2:C13=”Apple”)*(D2:D13
    250>250>

    Using Multiple condition count with OR:

    To count cells by using an OR criteria, you should use multiple COUNTIFS functions.  For example, counting the number of instances of  8,6 and 5 in the range B2:B13.
    =COUNTIF(B2:B13,8)+COUNTIF(B2:B13,6)+COUNTIF(B2:B13,5)

    This can be achieved through COUNTIF function array formula of Excel.  The below Array formula returns the same result as that of above. As this is a array formula you need to enter the formula using Ctrl+Shift+Enter.
    =SUM(COUNTIF(B2:B13,{8,6,5})

    Note:  You need use flower bracket for 8,6,5 in the above example. 

  • Excel COUNTIF function Syntax with Examples

    The Excel COUNT and COUNTA formula is useful for the basic counting requirements. However, sometime you need to count a number of cells in a range which meets given criteria. To do this, COUNTIF formula is useful. So COUNTIF formula counts number of cells in a range based on the given criteria.
    Syntax of  Excel COUNTIF formula:
    COUNTIF(range, criteria)
    where
    range is range of cells which you need to count
    criteria is nothing but the condition on which the range should be calculated.

    Example for Excel COUNTIF Formula:
    Let us take below sample data and use countif function with different criteria.

    Excel Examples

    =COUNTIF(C2:C7,50) :- Counts the range with Value 50
    =COUNTIF(C2:C7,E1) : Counts the cells value equal to Range E1
    =COUNTIF(C2:C7,”>”&E1) : Counts the cells greater than value specified in the range E1
    =COUNTIF(D2:D7,”=COUNTIF(C2:C7,”*”): Counts the cells which contains text
    =COUNTIF(A2:A7,”Apple”): Counts only the cells with exact word Apple; not case sensitive
    =COUNTIF(A2:A7,”*Apple*”): Counts cells containing text Apple anywhere within the text
    =COUNTIF(A2:A7,”*E”) : Counts cells which cell content has the alphabet E in the last
    =COUNTIF(B2:B6,”>”&AVERAGE(B2:B6)) : counts the cells which are greater than average of the range.

    Using COUNTIF function with named ranges:  You can use Named Ranges in Excel countif function.  For doing this first we need to define the range.  Let us look into the above example and define Range A1:A7 as ” “Data”.

    Excel Tips

    Now we can use the names instead of writing the Ranges. The example for Countif function with named ranges.
    =COUNTIF(Data,”Mango”)
    Similarly we can use defined names to any range by naming that range. It is useful when we use complicated countif functions.0>

  • Adding Facebook Like box or Facebook Fan box widget to Blogger

    Adding a Facebook Fan Box or Facebook Like widget to Blogger helps to get more Facebook fans. So here is a quick tutor to add Blogger widget for Facebook Like Box for the Blogger. Facebook Like Box was earlier called as Facebook Fan Box.
    Steps to Create  Facebook Like Box Widget for Blogger: The below mentioned steps given are assuming that you already created a Fan Page in Facebook. Hence in this post, I have giving only the steps to create the Blogger Widget for Facebook Like box.
    Step 1 : Go to Facebook and open your Facebook Fanpage URL. Copy the address of the fanpage from the URL Bar from the browser as shown below. In my case it is
    http://www.facebook.com/ExcelTipsAndTricks

    Facebook Tips

    Step 2: Go to the Facebook Social Plugin creator page by clicking Facebook Like Box configuration Page. You will get a page as shown below.

    Facebook Tips to add Gadget to Blogger  

    Step 3: Now paste your page URL  which you have copied in the step 1, inside the Facebook Page URL text box.
    Step 4: Customize the widget size by entering the height and width and color scheme.  Keeping the height field blanks takes takes the default size. You can select “Show Faces” Check box to show the images your followers else images will not be shown. You can also enable or disable recent post snapshot in the widget by selecting or un selecting the “Show Stream” Checkbox.
    Step 5: Click on Get Code which gives two sets of codes. Select “iFRAME”  in the top tab and copy the code.

    facebook Like Box Tips

    Step 6: In Blogger, go to “Layout” click on ” Add a Gadget” link. Select HTML/Javascript gadget and paste the code in the content box. Place the gadget in your desired place of the blog.
    Step7: Click on “Save Arrangement”. You are done. Your Facebook Like box appears in your blog.

    Adding the Facebook Like Box in the blogger helps to gain more Facebook fans and also it helps to improve user engagement on the blog. Let me know if this helps. Share your inputs in the comments section below and hit Like if you like this tip. 

  • Surya Namaskara- A complete Yoga for Every one

    Surya namaskara means saluting to lord Surya the source of energy to the world. Surya Namaskara is set of yogasana postures. It not only just physical exercise, it is a complete exercise for body, mind and soul. Daily performing Surya Namaskara Yoga postures has many advantages.

    Main Advantages of performing Surya Namaskara Daily

    Performing Surya Namaskar every day has many health benefits. Some of them are

    • Beneficial for the health of digestive system.Regular practice of Surya Namaskara helps to improve the digestive system. It stretches the abdominal muscles and helps to lose excessive belly fat and gives flat stomach.
    • Ideal exercise to cope with insomnia.Regular practice of Surya Namaskara calms the mind and helps to get sound sleep. Thus it helps in controlling insomnia and other related diseases
    • Practice regulates irregular menstrual cycles. Practicing Surya Namaskar ensures the easy childbirth. It helps to decrease the fear of pregnancy and childbirth.
    • Surya Namaskar practice boosts blood circulation and helps to prevent hair graying, hair fall, and dandruff. It also improves the growth of hair making it long.
    • Regular practice of Surya Namaskar helps to lose extra calories and reduce fat. It helps to stay thin. Practicing Surya Namaskar is the easiest way to be in shape.
    • Sun salutation exercise helps to add glow on your face making facial skin radiant and ageless. It is the natural solution to prevent onset of wrinkles.
    • Regular practice of sun salutation boosts endurance power. It gives vitality and strength. It also reduces the feeling of restlessness and anxiety.
    • The back muscles are toned up and strengthened to a great extent and the spine and arms become stronger
    • It regulates the respiration system and improves lungs functionality

    Procedure to perform Surya Namaskara

    Surya Namaskara consist of 12 postures. Ideally Surya Namaskara should be done total 13 times along with Surya Namaskara Mantras given below chanting one mantra before starting each cycle of yoga postures.

    Surya Namaskara Mantras

    1. Aum Mitraya Namah
    2. Aum Ravayre Namah
    3. Aum Suryaya Namah
    4. Aum Bhanave Namah
    5. Aum Khagaya Namah
    6. Aum Pushne Namah
    7. Aum Hiranyagarbhaya Namah
    8. Aum Marichaye Namah
    9. Aum Adityaya Namah
    10. Aum Savitre Namah
    11. Aum Arkaya Namah
    12. Aum Bhaskaraya Namah
    13. Aum Shri Savitra Surya Narayanaya namah

    Postures of Surya Namaskara

    As said earlier, Surya Namaskara is a set of many Yoga postures. Let us look into different postures of Surya Namaskara.

    Surya Namaskar Postures
    Step1: Stand straight with your feet together and palms folded at the chest as if in prayer. Your toes can be slightly apart but the heels should be as close as possible.
    Step 2:Raise your arms above your head and stretch back as much as possible to bend your spine backwards while inhaling air. Your abdomen should be stretched to the maximum limit and your biceps should be touching your ears with your hands parallel.
    Step 3:Bend forward, go down and touch your toes with your fingertips. Don’t bend your knees and try to touch your knee with your forehead. When you do this, your stomach is compressed, so exhale.
    Step 4:take your right leg back and stretch it while balancing it on the toe and keep your left leg folded in front of your chest. Keep your palms straight on both sides of the left foot on the ground. Look up and inhale.
    Step 5:Take back the left leg as well and keep both the feet together while raising your hip from the ground and balancing yourself on all fours. You will look like an inverted V. Exhale while you do this posture.
    Step 6:Slowly come down and rest your palms next to your shoulders, your knees touching the ground but your waist and hip slightly raised above so that they don’t touch the ground. Your face should also be downward facing and hold your breath during this.
    Step 7:Lower your waist and raise your torso, making your arms straight and balancing yourself. Feel your spine bend and the abdomen stretch. Inhale when you do this and look straight.
    Now, again raise your hip and balance yourself on all fours. Looking like an inverted V. Exhale while you do this.
    Step 8:Bring the left leg forward and fold it so that the knee reaches your chin and look up. Keep the right leg behind as before and stretch. Inhale when you do this.
    Step 9: With your palm at your toes, raise yourself up so that your forehead touches the knees and your spine bends forward. Exhale in this posture.
    Step 10:Get up very slowly and take your arms above your head and stretch backwards while inhaling.
    Step 11:Then come back to the first position with your arms folded and relax while breathing normal.
    Step 12:Repeat the series with the other leg.

  • Samsung Galaxy S Duos Review Specification Price

    Samsung Galaxy S Duos S7562 is a mid range Dual sim Android smartphone. It is powered by 1GHz Cortex A5 processor with 768MB of RAM and Adreno 200 GPU. The device measures 63.1×121.5×10.5 mm and weighs 120 grams. The overall design of the phone is much inspired by the Samsung Flagship model Galaxy S3. Samsung Galaxy S Duos price in India is ₹9,599.
    Samsung Android Dual sim phones

    Samsung Galaxy S Duos comes with 5MP primary camera and VGA front facing camera. The camera has features such as auto focus, panorama and photo effects, 2X digital zoom along with LED flash. The dual sim Android smartphone capable of shooting 480p videos at 30fps.Internal memory of the Galaxy S Duos 7562 is 4GB which can be further increased by adding microSD card upto 32GB. The device comes with 1500mAh battery which will provide talk time upto 12 hours and 570 hours of standby time.

    Samsung Galaxy S Duos supports GPRS, EDGE, Wi-Fi and Wi-Fi Hotspot, Bluetooth and USB connectivity. It also supports GPS with AGPS service. The S7562 is bundled with popular apps such as Chat ON, SNS Integration, Play Store, Samsung Apps, Google Search apps.
    Samsung Galaxy S Duos S7562 is going get Jelly Bean update soon. The updates can be installed through OTA or using the Latest Samsung Kies. With the help of Samsung Kies, the device can be used as modem through USB tethering. The devices with WiFi connectivity can be easily connected through the WiFi Tethering features. Like any other Galaxy Device, this device to support Samsung Mobile PC suite Kies 2.6.  It is highly recommended to have Samsung Kies installed in your PC as this device at time requires firmware updates through PC only.

    Merits and Demerits of Samsung Galaxy S Duos S7562

    Merits of Galaxy S Duos:

    • Decent snappy processor with 768MB RAM
    • Dual sim support with Smart Dual feature
    • Front facing camera support
    • Stylish and slim design
    • Excellent battery life for Android Standards

    Demerits of Galaxy S Duos:

    • No HD video recording support. It supports on 480p videos
    • The touch response is not that great
    • Phone hangs some times
    • No Dedicated shutter key
    • No Ambient Light Sensor

    Samsung Galaxy S Duos Specification Summary

    • Runs on Android 4.0 ICS OS. Upgradable to Jelly Bean
    • 1GHz Cortex A5 processor with 768MB RAM and Adreno 200GPU
    • 4 inch TFT capacitive touch screen with WVGA 480*800 pixel resolution
    • 5MP auto focus camera with LED flash and VGA front facing camera
    • Dimensions- 63.1×121.5×10.5 mm and weighs 120grams
    • Accelerometer, Proximity Sensor, Compass sensors
    • 4GB internal memory and expandable memory support upto 32GB
    • 1500mAh battery with talk time upto 12 hours and standby time upto 570 hours.
    • GPRS, EDGE, Wi-Fi and Wi-Fi Hotspot, Bluetooth and USB