Search

# Eight ways to exploit COUNTIF() … and sumif() too [spreadcheats]

Share

If for every countif() I write excel paid me a dollar, I would be a millionaire by now. It is such a versatile and fun formula to work with that I have decided to write about it as third post in our spreadcheats series.

1. Using COUNTIF() to replace pivot tables: We all know that you can use countif() to replace pivot tables for simple data summarization. For eg. if you have customer data in a table and you would like to know how many customers you have in each city you can use countif() to find that.
`countif("master-data-range","city name")`
More on this method of using countif and 4 other ways of using excel if () formulas
2. Counting Valid Phone Numbers in a Range: Using operators < and > in countif() you can findout valid phone numbers in range like this: `countif("data-range",">"&1000000000)`
3. Finding number of customers in a city based on their phone number: This trick may not work perfectly. We can use `countif("data-range","22*")` to findout total customers in Mumbai (since all Mumbai phone numbers begin with 22)
Note: This method works as long as phone numbers have identifiable calling codes and stored as text. To covert a number to text you can use text() or append an empty space to the number.
4. Pattern matching: Often when you extract data from other sources and paste it in excel it is difficult to process it when the formats are not consistent. For eg. when you copy address data of a bunch of customers and need to know how many customers are in “New York” you can use countif like this: `countif("data range", "*new york*")`, the operator * tells excel to match any cell with new york in it, not necessarily at the beginning or end of the cell.
5. Counting positive numbers in a range: Again we use the > operator to count the positive numbers in a range like this: `countif("data-range",">0")`.
A very good use of this trick is when you need to calculate average of a bunch of numbers but need to exclude zeros: `sum("data-range")/countif("data-range","<>0")`
6. As a replacement to FIND(): Excel FIND() is powerful formula to find if a particular text occurred in another text. But one problem with find is it returns #value! error if the value you are looking for is not in the input cell. What if all you need to know was whether your cells had a particular value or not?You are right, you can use COUNTIF() for that too, like: `countif("cell-you-want-to-look","*hilton*")` will return 1 or 0.
7. For sorting text: Read more on this at sorting text using excel formulas
8. Findout the number of errors in a sheet: The beauty of countif() is that you can even count error cells. For eg. you can use it like: `=COUNTIF(1:33,"#VALUE!")` to findout how many #VALUE! errors were there in the rows from 1 to 33. This can be useful if you are building a complex model and need to keep track of errors.

Most of the tricks should work with SUMIF() as well.

If you like this, read the other posts in the spreadcheats series. It is a 30 post series (3 posted so far) that aspires to make YOU very good in using excel to solve day to day problems.

### Get FREE Excel + Power BI Tips

Simple, fun and useful emails, once per week.

Learn & be awesome.

### Welcome to Chandoo.org

Thank you so much for visiting. My aim is to make you awesome in Excel & Power BI. I do this by sharing videos, tips, examples and downloads on this website. There are more than 1,000 pages with all things Excel, Power BI, Dashboards & VBA here. Go ahead and spend few minutes to be AWESOME.

Read my storyFREE Excel tips book

Overall I learned a lot and I thought you did a great job of explaining how to do things. This will definitely elevate my reporting in the future.
Rebekah S
Reporting Analyst

From simple to complex, there is a formula for every occasion. Check out the list now.

Calendars, invoices, trackers and much more. All free, fun and fantastic.

Power Query, Data model, DAX, Filters, Slicers, Conditional formats and beautiful charts. It's all here.

Still on fence about Power BI? In this getting started guide, learn what is Power BI, how to get it and how to create your first report from scratch.

### How to calculate WEEKNUMBER in Month / Quarter / Year with Excel?

Let’s say you have daily data and your boss wants to see the trends by week in month or week in quarter? How do you calculate the week number in a month or quarter? In this article, let me explain the logic and formulas we can use Excel for this.

## Related Tips

Charts and Graphs

Learn Excel

Learn Excel

Learn Excel

Excel Howtos

### How to fix SPILL Error in Excel Tables (3 easy solutions)

Financial Modeling

### 85 Responses to “Eight ways to exploit COUNTIF() … and sumif() too [spreadcheats]”

1. DBrueggeman says:

Chandoo -
I'm on of those LifeHackers that have invaded your blog. I must say that I love the simple style you use - I've already pointed a number of coworkers to your blog for hints and tricks to relieve myself of the some of the work that comes with being the resident Office guru.
One thing that would be great for this series is a review of v/hlookup vs. the Index/Match combination. I can always tell when someone ready to go to the next level when they discover vlookup but using that command really pumps up the file size in older versions of excel (pre '07). Index(entire range, use Match on the vertical list, use Match on the horizontal list) is so much more versatile - you can change the order of columns, rows, and pick out items that are left of the lookup. It also is very easy on the file size (a plus when you have to email files under the draconian limits of the mail attachment size.

2. Chandoo says:

@DBrueggeman: Welcome and thanks alot for the sweet words 🙂

I am soooo happy you were able to use some of this and sharing it with others.

I am going to write about offset / vlookup as part of the spreadcheats, may be this week. I hope some of these questions can be answered.

• Chahine says:

first when i read your website name i found it funny i thought what that chandoo can do with that similar name but when i continue reading your work and find out how its pro and well explained im surprised man and be sure you are the first and only one in my bookmarks concerning Excel !!
you are one from few peoples that are giving and helping without asking anything in return
all the best for you and for Chandoo.org
Regards,
Chahine

3. [...] name and the list is sorted on column B, we can use offset() and match() combination along with countif (ahem!) to determine which range to use for project cell [...]

4. Myea Amelia says:

This is the first place i search when i have some difficulty in excel.
Keep writing ...

• Chandoo says:

@Myea: Thank you so much. I am happy you like my site and use it very much 🙂

5. Raj Kiran says:

Hi Chandoo,
I am trying to provide summary analysis from a Raw data for eg production tickets. Here I would like to display a table which shows no. of High, Medium and Low issues with respect to OPEN, CLOSED and HOLD parameters. I wanted to do without using excel pivot tables. Can you please let me know the way on doing this.

6. Chandoo says:

@Raj: Assuming you have a production ticket table like this:

A - B - C
1 Ticket - Priority - Status
2 t1 - high - open
3 t2 - low - hold

etc.

You can add one more column in the end with the formula: =b2&c2

Then, create another table like this:

E F G H
1 [ ]High Medium Low
2 Open
3 Closed
4 Hold

then in cell corresponding to high, open you can write a formula like,
=countif(d2:d100,f\$1&\$e2)

copy paste the formula over the 8 remaining cells and you should have what you are looking for.

let me know if you are facing some difficulty

7. Raj Kiran says:

Hi Chandoo,

Thank you very much. It worked.

8. joanne` says:

I am trying to do a survey of data with count if. i can't seem to do it, i need help. thanks

9. Chandoo says:

@Joanne' did you check out our how to do employee satisfaction surveys in excel post?

chandoo.org/wp/2009/02/04/satisfaction-surveys-excel/

10. Dear Chandoo,
Given a table with fields for invoice dates and invoice total, I want to take a years worth of data and break it into monthly sales to make bar graphs.
I am trying and failing to get sumif to help with out luck
I read with interest your tutorial on countif but it is still beyond me.
Any idea?

Forrest

Data:
No Date Contact Name Subject Description Total Account Name
382 2007-01-12 FE Twinkle - \$412.99 Sleep
316 2007-01-25 fi Twinkle Evaluation of Text Inserter Setup with GSITE software. V1_22 and RJ45 adapters. \$446.96 Sneesy
317 2007-01-25 fo Little Evaluation of Text Inserter Setup with GSITE software. V1_22 and RJ45 adapters. \$446.96 Bashfull
345 2007-02-05 fum Star - \$853.96 Mickey
362 2007-02-15 I How - \$426.98 Minny
408 2007-02-15 Smell I - \$853.96 Pluto
412 2007-02-21 The Wonder - \$426.98 Donald

11. Tania says:

Hi, I'm having problems with SUMIF when taking values from other excel files: if the files are not open it gives me error "#value!". Since I'm using about 50 different files I can't open and close all of them all the time (even doing it with woorkbooks_open/close).
Any suggestion?

• Chandoo says:

@Tania: Working with several files is a very expensive operation. But it is still possible. By using the full physical path of the file in the reference of SUMIF you can get the contents of the files even when they are closed. Let me know if you face some difficulty implementing this.

12. Chandoo says:

@Forrest: You can use the month() function to extract month from any date. So create a new column where you can extract this month data and then pass it to sumif().

13. Kyle says:

I discovered your site about 5 months ago and use is everytime i hit a hurdle in Excel or need creative graphing ideas. Because of your site my bosses consider me our teams Excel expert. I think what makes this site the best resource for intermediate Excel users like me is that you explain everything simply and in a stepwise fashion and then you make available the ground covered in an Excel file which i can save and against which i can compare my own interpretation of the tip using my own data. I guess that's just a long way of saying thanks a mil, man!

14. Chandoo says:

@Kyle.. You are welcome.. I am happy you enjoy the site.

15. [...] 8 ways to use SUMIF() and COUNTIF() formulas [...]

16. [...] Learn cool uses of countif and sumif [...]

17. [...] excel formulas: IF and Then, Vlookup, Offset, Sumif, Countif, Working with date and [...]

18. [...] the sales person named “Luke” sold. This is simple, you will write a SUMIF formula [examples] and use the Name column as “criteria range” and Sales column as “sum [...]

19. [...] Using COUNTIF and SUMIF formulas [...]

20. [...] 19: Introduction to COUNTIF and SUMIF Formulas [...]

21. [...] During a recent consulting work with a client I had a requirement to sum up values that meet some criteria across columns and I wasn’t sure if the SUMIF would hold. Boy, I was wrong. It worked nicely and I still stand by my statement “If I get a dollar for every COUNTIF / SUMIF I write, I will have million dollar bills.“. [...]

22. izabel from brazil says:

I love this site and recomend to all my friends.
Now I need an help me, please
I'm trying to use the CountIf() with ">= and =1 and <=3"))

month \$
9 \$ 1,00
1 \$ 2,00
2 \$ 1,00
8 \$ 3,00
3 \$ 5,00

tks again Izabel

23. Chandoo says:

@Izabel... Thanks for your comment and love. Welcome to PHD.

You can write SUMPRODUCT formula like this:

=SUMPRODUCT(- -(ROUNDUP(A1:A10/3,0)=1)) to find the count of all the values =1

replace the =1 with =2 to get =4.

24. [...] we know how to find sum of values that meet a criteria – we use either SUMIF, SUMIFS or SUMPRODUCT [...]

25. [...] Also, you should know how to use COUNTIF Excel Formula, it is so awesome, I wonder why MS hasnt called it MAGIC() [...]

26. [...] to use the intermediate level functions within Excel. These include: sum, sumif, macro’s, logic (if, then, else), auto sum, filtering, auto subtotal, sorting, charting, pivot [...]

27. Thameem says:

No Revenue Duration
316575505 23.63 207.60
216552606 78.07 689.80
23880712633 36.73 316.50
316364514 0.32 2.50
216404819 45.87 632.57
2016818256 51.16 705.95
2416540148 57.73 899.65
2316935400 45.60 383.75
291880656194 6.45 80.53
2416378059 8.23 64.95
2336497000 10.26 89.20
2346126773 13.58 193.97
2356261454 0.41 4.22
231980547017 7.00 60.15
231995883 107.48 746.78
316105223 6.90 89.57
4316739635 6.80 96.17

I want do calucate the band wise like the below. I want count how many numbers are in the below band and their sums as well. Kindly advice me, which formula to use.

Score Bands
0
1
2
5
10
15
20
25
50
100
>=100

28. [...] COUNTIF & SUMIF Formulas [...]

29. Edward Gitau says:

hi,
I am really having a problem with Data comparison and your blog has been like an eye oppener to me so thank you.
I have like a "special request", do you mind telling me how i would go round the issue i am currently facing? I have two systems at the office. when i copy Data from one place and paste it to another the Data formats are slightly different. one is 07:00:00 AM and the other is 07:00:00 with no AM nor PM you basically have to do that on your own.
I do nto have any strong knowlage with excell so please bare with me. so the issue is i need a formula that will look at Data in cell A and B then if there is a match it higlites them. Or if it were possible whenver it could get a match, it pastes the names(that match) from cell C and pastes them to D.
your soonest assistance will be appreciated. My job almost depends on this.

30. Hui... says:

@Edward
Times are stored as numbers between 0 Midnight and 1 Midnight with 0.5 as Mid Day
.
Display Format
The way you see them is controlled by the display Format
.
Select the cells
Ctrl 1 or Right Click and Format Cells
On the Number Tab
Select the display Format you want
Close
.
http://chandoo.org/wp/2008/02/25/custom-cell-formatting-in-excel-few-tips-tricks/
or
http://chandoo.org/wp/tag/number-formatting/
.
You can also use the Format Painter to copy cell formatting from one area to the other
Its the small paint brush on the Home, Clipboard tab
.
Conditional Formatting
To highlight where cells match you want to use Conditional Formatting

31. Nico says:

Hi Chandoo,

Checked your site on a "countif" issue, but could not find a solution.
My problem is dat in de data I have names (not months) names starting with MAR. eg MAR01 and MARC1.
Applying a count if on this data adds up both names as they are both march-01. But in really MAR01 and MARC1 are both names for totally different groups.
I already converted to text, added ' in front, but nothing worked... do you have a solution?

Nico van Bergen

• Nico says:

I noticed some recent activity on the countif formula. Still the reactions do not solve my problem as described above. Let me show an example:

MAR-List
Criterium
=COUNTIF(A2:A5,B2)

MARC1
MARC1
4

MARC1

MAR01

MAR01

How can I prevent that Excel reeds dates in column A instead of names?

Thx, Nico

• Nico says:

Second try for better lay out:

List, Criterium, Formula (COUNTIF(A2:A5,B2))
MARC1, MARC1, 4
MARC1
MAR01
MAR01

As the formula counts all  "MARC1" I would expect the result being 2, not 4.

Rgds, Nico

• Hui says:

@Nico
Thats interesting and I can't explain why Marc1 seems to be equal to Mar01 ?
However if you use =SUMPRODUCT((A2:A5=B2)*1)
It works correctly

• Nico says:

@Hui, thanks for this solution!

32. Krishna says:

Hi Chandoo,

I am trying to count the number of previous cells that are greater than zero. The tricky part is it should stop counting if hits a previous cell with zero value. Which means at current cell it should count the values greater than zero upto a point where the previous cells have greater than zero values and stop counting when one of the previous cells have a zero value.

• Hui... says:

@Krishna

Can you please post or email me a sample of the data you have with some instructions

• Krishna says:

Hi Hui,

Thanks for your reply. Below is the sample data with manual calculation.

The date and value column are the one user provides and the count column should be generated with a "COUNTIF" of "SUMIF" formula such that at each cell it calculates the count of values greater than zero for previous cells until it reaches one of the previous cell that has a zero value. Basically I would like to know how many values are greater than zero before.

Date     Value   Count
1/1/201210
1/2/201201
1/3/201200
1/4/201200
1/5/201200
1/6/201210
1/7/201211
1/8/201212
1/9/201203
1/10/201210
1/11/201201
1/12/201200
1/13/201200
1/14/201200
1/15/201200
1/16/201200
1/17/201210
1/18/201211
1/19/201212
1/20/201213
1/21/201214
1/22/201201
1/23/201200
1/24/201200
1/25/201200
1/26/201200
1/27/201200
1/28/201210
1/29/201211
1/30/201212

Thanks.

• Krishna says:

I see that in my previous post the columns are not seen with spaces. I have inserted commas to differentiate between each columns.

Date      Value   Count
1/1/2012,1,0
1/2/2012,0,1
1/3/2012,0,0
1/4/2012,0,0
1/5/2012,0,0
1/6/2012,1,0
1/7/2012,1,1
1/8/2012,1,2
1/9/2012,0,3
1/10/2012,1,0
1/11/2012,0,1
1/12/2012,0,0
1/13/2012,0,0
1/14/2012,0,0
1/15/2012,0,0
1/16/2012,0,0
1/17/2012,1,0
1/18/2012,1,1
1/19/2012,1,2
1/20/2012,1,3
1/21/2012,1,4
1/22/2012,0,1
1/23/2012,0,0
1/24/2012,0,0
1/25/2012,0,0
1/26/2012,0,0
1/27/2012,0,0
1/28/2012,1,0
1/29/2012,1,1
1/30/2012,1,2

• Hui says:

@Krishna

Interesting problem

I will have to have a think about it.

You may want to post the question on the Chandoo.org forums
http://chandoo.org/forums/?new=1

• Chandoo says:

Interesting question as Hui says.

I may have a solution.

Assuming your data is in A1:B20 (A with dates & B with the numbers)

in C2, write =IF(B1=1,C1+1,0)

and drag it down.

In C1, write =IF(B1=1,0,1)

This is producing same results as yours except for cell C22 (in your data above, you say the value should be 1, where as formula shows it as 5)

• Hui says:

I Must be having a bad morning to miss that 🙁

• Krishna says:

Chandoo,

Thank you very much...It works like a charm....You are awesome

Thanks Hui.......I appreciate you both for taking time in answering my question...

Thanks,
Krishna.

33. Jeff says:

You have some useful info here.  I am tryng to use COUNTIF to count the number of < and = signs, the actual operators themselves.  The ~ that you can use to put before special characters, like * and ? does not work for < and =.

34. Giedrius says:

Hey, a lot of useful info here. Maybe do You know, how to sum numbers(money spent)  if there is a word "food", but cell to sum with a number is from the right of the cell with a word.

35. lwatson814 says:

Having trouble with a formula that uses multiple if/then statements.  It is a multiple rate structure that is base on a given variable.  IE....if a customer uses 25,000 gallons of water he is charged based on the rate structure of usage between 0 and 3,000 gallons, and another rate for 4-10,000 gallons and then 11-20,000 gallons and then over 21,000 gallons.
Any ideas....I am just a basic excel user...haven't used all the functions before.

• Hui... says:

Lwatson814

Generically this style of problem is solved by something like:
```=IF(B2<=3000,Fixed 1 + B2* Rate 1,IF(B2<10000,Fixed 2 + (B2-3000)* Rate 2,IF(B2<20000,Fixed 3 + (B2-10000)* Rate 3,Fixed 4+ (B2-20000) * Rate 4))) ```

Where you accumulate the rates up to your value as a fixed amount then multiply the difference by the rate above that amount

• lwatson814 says:

Thanks...I will try that and see how that works.

36. Afsar Ali says:

hey
em afsar ali from pakistan.
I found your tuturials very informative and helpfull.
I want you to help me out regarding duplicate entry in excel.
what I need is to prevent duplicate entry of clients when any two or three cells in the row matches with another cells in the other row by giving a warning messege at your ealier considrations. I need this despretly..
waiting......

37. [...] wildcards in COUNTIF formula page to get some [...]

38. shahbaz says:

i have duty schedule i want to asign like d=12hr, d*=24hrs

is it possible to make it onexcell sheet.

shahbaz

39. Selvam.V says:

Thanks chandoo. I practiced & worked well.....

40. arul says:

pls clarify my doubt

excel

column (a1:a20) ten boys girls name
column (b1:b20) some caste example : sc,bc,mbc,oc,st,ss

my question

how to count the no.of male in sc/bc/mbc/ etc......

41. arul says:

pls clarify my doubt

excel

column (a1:a20) ten boys and ten girls name
column (b1:b20) some caste example : sc,bc,mbc,oc,st,ss

my question

how to count the no.of male in sc/bc/mbc/ etc……
and no.of female in sc/bc/mbc/ etc……

• Hui... says:

@Arul

What data defines a Boy or Girl ?

42. arul says:

Sir

Sn Name Sex Caste
1 Arul Male MBC
2 Jothi Female BC
3 Kumar Male SC
4 Anitha Female BC

MY Question

1. The Above Excel data typed the lot of students list iam asking for
How to Count the No.of Male in SC/BC/MBC/...???

How to Count the No.of Female in SC/BC/MBC/...???

43. Mike says:

Hello,
I really like how you did the formula for tracking the repeating customers at a store for the last 30 days.
=IF(C8>TODAY()-30,IF(SUMPRODUCT(--(\$B\$4:\$B\$53=B8),--(\$C\$4:\$C\$53>(TODAY()-30)))>1,"Repeated","Not Repeated"),"Not Repeated")

I am trying to track license plates for the last 30 days, and making a column that will automatically count the number of times a license plate shows up in the last 30 days. Can I get any help with that? Thank you very much for a great website.

44. Mike says:

Oh yeah, I would also like the cell to change color (yellow if 9, and red if 10 or above). I can do this part on my own now, but to add it to an existing formula, is what I am not sure about.

Again, thank you for any help.

45. VJ221 says:

=SUMIF('[filename1.xlsx]Sheet1'!\$A:\$A,C12,'[filename1.xlsx]Sheet1'!\$Y:\$Y)+SUMIF('[filename2.xlsm]Data from ABC'!\$A:\$A,C12,'[filename2.xlsm]Data from ABC'!\$Y:\$Y)

The above formula is being used in 'filename3', however, if filename1 and filename2 are closed it gives and error and I have to set the entire formula again. To avoid this how can we give the file name as a cell reference

I am using the following formula, wherein I have given the entire file name in cells A10, A11, B10, B11, but it is not working.
=SUMIF(A10,C12,A11+SUMIF(B10,C12,B11)

Can you please suggest a crack to this?

46. Ana Preston says:

Help!

I am trying to calculate by using countif for the number of "EZ". I want the formula to return a number value if the count if is >10 <26. using the following:

EZ
EZ
EZ
12
EZ
6
3
EZ
EZ
EZ
EZ
EZ
6
EZ
3
3
EZ
EZ
EZ
EZ
EZ
EZ
12
EZ

• Hui... says:

@Ana

Not really sure what your after but the basic formula is:
=COUNTIF(B2:B25,"EZ")
In this case it returns 17

47. Ana Preston says:

Yes, but I want the formula to return the value that when they are counted is greater than 10 but less than 25. In this case it would be 7. Do you know how I can write that formula??

Thanks!

• Hui... says:

What if it is less than 10 or greater than 25?

• Ana says:

Well.....

Ok, since you asked! 🙂 I would like 3 separate formulas: One that returns the count if the value is 10 or less, then the next would be the count if it is greater than 10 but less than 26 and then the final wouns be greater than 26 but less than 51. This is going to be used to calculate commissions at 3 different levels.

I appreciate any help or guidance you could provide!

Ana

• Hui... says:

@Ana

You will want something like:
=IF(COUNTIF(B2:B25,"EZ")<10, 2,IF(COUNTIF(B2:B25,"EZ")<27,1,0))

Where it will return 2 if the Count is <10
It will return 1 if the coun is less than 27 and 0 (when the count >=27) other wise

So in your case you want the count minus 10 if it is between 10 and 27
=IF(COUNTIF(B2:B25,"EZ")<10, 2,IF(COUNTIF(B2:B25,"EZ")<27,COUNTIF(B2:B25,"EZ")-10,0))
Note I have replaced 1 with COUNTIF(B2:B25,"EZ")-10

48. Ana Preston says:

Hui,

You're the bomb! I have everything I need now. Thanks for all your patience and help.

49. Ana Preston says:

HUI,

Sorry....I should have posted my formulas for anyone else that was following. Here are the 3 formulas I used:

=IF(COUNTIF(E2:E92,"EZ")>0,IF(COUNTIF(E2:E92,"EZ")10,IF(COUNTIF(E2:E92,"EZ")25,IF(COUNTIF(E2:E92,"EZ")<51,COUNTIF(E2:E92,"EZ")-25,50))

They all work!
Thanks again.

50. i want to ALL Short key Advanced Excel.

51. srishti says:

Using formula calculate How Many Names are in below mentioned Range

10
Sumit
20
Ram
30
Rohan
40
Mohan
50
Reeta
60
Swati
70
Poonam
80
Mukesh
90
Shailender
100

• Hui... says:

@Srishti
Assuming the range is A1:A20

You can use:
=COUNTA(A1:A20)-COUNT(A1:A20)

Or a simpler formula is:
=COUNTIF(A1:A20,"?*")

52. Phyo Wai says:

In a class, the name start with Maung, Saw, Min & Khun are Male and Ma, Nan,Mi are Female. How to separate Male or Female using excel 2007. Please give me answer for my Question. Thank you .

53. dharmendra says:

HI.....
chandoo, org im really thak to you for sending new new formulas with examples i hope in furture too....

54. abhishek tiwari says:

hey we have 4 to 20 no for experience such that if one man complete 6 months he can get 4 marks in other way from 6 months to 17 months he get 4 marks and after that from 17 to 27 months 8 marks and onwards plz sujjest if formula for such case

55. Amol says:

Dear chandoo,

I want to count the cell which are green colored in between the particular range of cells. for ex: (A1:D20) In which may cells are blank and some cells are green and we can not use filter to count.
How can i able to count those cells by Using formula is there any way to count it??

Having a problem! I need to find the threshold (999999.99) of revenue and add 14% vat once this has been reached. I have this at the moment =(C9>=999999.99,0.14*C9,0.14*C9) and this returns the value for all amounts. I only need it for threshold.

57. blossoms_005 says:

Hi,

I need to get the shift timing for the below dates as:
if the time range is from 06:00 - 15:00 then it should be "1st shift"
if the time range is from 13:30 - 22:30 then it is "2nd shift" etc

Date
4/2/2017 21:39
3/2/2017 19:23
2/10/2017 22:45
3/7/2017 6:27
1/24/2017 11:37
4/3/2017 10:31
4/1/2017 12:08
3/10/2017 10:06
3/6/2017 6:40
3/3/2017 10:27
2/28/2017 21:28
2/15/2017 7:20
2/6/2017 4:43
4/30/2017 22:52
4/30/2017 22:05
4/30/2017 20:22
4/28/2017 10:58
4/25/2017 23:09
4/25/2017 10:24

Tried using IF but no lock.
Any help would be of great help. Thanks in advance!

• Hui... says:

assume 4/2/2017 21:39 is in A2:

B2: =if(mod(B2,1)<(15/24),"1st Shift","2nd Shift")

58. Dankos says:

i have a little problem which i want solution.
i have a table like this:
a,b,c,d,j on separate column
e,a,d,f,m
g,h,c,a,s
b,d,g,e,d

how will i look for the pairs in this table
because the pairs here are
a,d
c,a
d,e

and another one is
if they are figure like
10,20,21,26,80
10,45,42,44,80
15,21,23,26,32
45,42,74,76,50
26,10,45,88,89
which formula can i use to bring out all the pairs in the list of items
using cell A1:E5 as the cell range

59. Mohammed says:

Hi Chandoo.

I wanna ask how to count the numbers of training in each affiliate and warehouse. and the formula will be in other sheet of excel.

Warehouse Training Name Training Type
Safco SHEM 10 Affiliate
Ibb SHEM 01 Warehouse
Albayroni SHEM 11 Warehouse

• Chandoo says:

@Mohammed... Interesting question. But I am not sure I understand all of it. Can you tell me what should be the sample answers?

### Get FREE Excel & Power-BI Newsletter

One email per week with Excel and Power BI goodness. Join 100,000+ others and get it free.