## Wednesday, April 18, 2012

### Sum and count IF in same Function (CountIFS)

This can be use more in cricket stats to identify the count of 50's & 100's made by the player, while using the countif only the value greater then 50 comes, to ignore the score more than 100's.

Formula for counting 50's and 100's :
For 50's and not counting 100's : = COUNTIFS(F4:F135,">=50",F4:F135,"<100") for 100's : = COUNTIF(F4:F135,">=100")

COUNTIFS Explaination:
The COUNTIFS function, similar to Excel's COUNTIF function, counts up the number of times data in two or more ranges of cells meets multiple criterion.

The syntax for the COUNTIFS function is:
=COUNTIFS( Criteria_range-1, Criteria-1, Criteria_range-2, Criteria-2, ...)

Criteria_range - the group of cells the function is to search.
Criteria - determines whether the cell is to be counted or not.
Note: Up to 127 range/criteria pairs can be entered into the function.

The ranges must be of identical size, and the function only counts instances where the criterion for each range is met simultaneously - such as in the same row.