LikeOffice    Excel Help

EXCEL Utility
Customized Ribbon

- Compare worksheets
- Database analysis
- Stock to your Excel
- Password recovery
- and more...
Excel Formula
List of Excel
Formula examples
 
Excel Question
Ask us an Excel Question

 

Sumproduct Lookup Sumif Countif Unique Percentile

 

1. Sum based on one Condition with * at the end    
2. Sum based on one Condition with * at the beginning    
3. Sum based on one Condition which is exactly 4 letters text    
4. Sum based on one Condition with * in another cell    
5. Sum based on one Condition which is higher than five (>5)    
6. Sumifs with 2 or more conditions    





1. Sum based on one Condition with * at the end

Sum based on one Condition with * at the end.
It sums Column H based on every text that starts with 'Col'.

Formula:

=SUMIF(RNG_C,E12&"*",RNG_H)

E12 = Col

Answer = 128

Top

2. Sum based on one Condition with * at the beginning

Sum based on one Condition with * at the beginning.
It sums Column H based on every text that ends with 'o'.

Formula:

=SUMIF(RNG_C,"*"&E12,RNG_H)

E12 = o

Answer = 35

Top

3. Sum based on one Condition which is exactly 4 letters text

Sum based on one Condition which is exactly 4 letters text.
It sums Column H based on every text that has EXACTLY 4 letters.

Formula:

=SUMIF(RNG_D,E12,RNG_H)

E12 = ????

Answer = 123

Top

4. Sum based on one Condition with * in another cell

Sum based on one Condition with * in another cell.
It sums Column H based on every text that contains 'p' in it.

Formula:

=SUMIF(RNG_C,E12,RNG_H)

E12 = *p*

Answer = 226

Top

5. Sum based on one Condition which is higher than five (>5)

Sum based on one Condition which is higher than five (>5).


Formula:

=SUMIF(RNG_F,E12,RNG_H)

E12 = >5

Answer = 274

Top

6. Sumifs with 2 or more conditions

Sumifs with 2 or more conditions.
Excel 2007's SUMIFS function is similar to the SUMIF function except that it is used to add up data only if specific criteria are met in two or more ranges of cells.

Formula:

=SUMIFS(RNG_H,RNG_C,E12,RNG_E,F12)

E12 = cola2
F12 = b

Answer = 79

Top

7. Getting row that meet 2 or more conditions

Getting row that meet 2 or more conditions.
Getting row that meet 2 or more conditions.
This MATCH formula, as an ARRAY (CTRL-SHIFT-ENTER), return the row number once meet the following conditions:
It has 'a' in col. E and '11' in col. F
You can use concatenate (&) to add more conditions.

Array Formula:

{=MATCH(E12&F12,RNG_E&RNG_F,0)}

E12 = a
F12 = 11

Answer = 4

Top
 




Microsoft® and Microsoft Excel® are registered trademarks of Microsoft Corporation.
©2003-2011 LikeOffice - Privacy Policy - Contact us

[Top]