Geekzone: technology news, blogs, forums
Guest
Welcome Guest.
You haven't logged in yet. If you don't have an account you can register now.
Buying anything on Amazon? Please use the Geekzone Amazon aff link.




1 post

Wannabe Geek


Topic # 105910 13-Jul-2012 16:19 Send private message

Hi there,
I am after some help from an excel expert for a formula that has me stumped.
I have a tracking spreadsheet with two tabs, tab one is the main results page and the other tab is the "tracking" page.
I have a dates column in the tracking page where the result is counted once a date is entered.

I have used this formula to report this through to the main page like this =COUNTIF('OTRs'!I5:I30,"<01/10/12")

My issue is the main tab I need to show these result for different quarters. 1st quarter 1July-30Sep, 2nd quarter 1Oct-31Dec and so on.

I have these results counting once a date is put in, but I now need to be able to put a formula in each quarter section so it will only pick up the dates for that particular quarter so somehow say "between 1Oct and 31 Dec" etc etc, in addition to my existing formula.

Hopefully this makes sense, I am terrible at explaining.

If anyone can help out that would be fantastic!

Create new topic
148 posts

Master Geek


  Reply # 655348 14-Jul-2012 08:05 Send private message

Hi melsan

It sounds to me like you want to count a result if it satisfies multiple criteria. Using COUNTIF will mean you are restricted to one.

Have you tried COUNTIFS for multiple criteria?

60 posts

Master Geek
+1 received by user: 14


  Reply # 655358 14-Jul-2012 09:42 Send private message

Perhaps something like the following?

This is an array formula, so in my example enter the formula =SUM(IF($A$2:$A$25>=C2,IF($A$2:$A$25<D2,1,0))) (ie. without the curly brackets) in cell E2, enter it using CTRL+SHIFT+ENTER (which will enter the curly brackets), then copy down.

If you edit the formula, always enter it using CTRL+SHIFT+ENTER.


60 posts

Master Geek
+1 received by user: 14


  Reply # 655802 15-Jul-2012 08:29 Send private message

Thinking about this further, it occurs to me that there are (at least) three errors in my formula above.

Anyone want to point out what those errors might be?

2884 posts

Uber Geek
+1 received by user: 190

Trusted
Subscriber

  Reply # 655828 15-Jul-2012 09:10 Send private message

Here is another approach that you might want to consider.



In this example columns D and E are a definition of each month of the year and which quarter it should belong to. For example January belongs in quarter 1, December belongs in quarter 4, etc.

If you look at my formula that I have filled throughout column B, it examines the date of a particular transaction and uses the lookup table to determine the quarter in which the transaction occurred. You can then use column B as the criteria for a COUNTIF or SUMIF formula.

60 posts

Master Geek
+1 received by user: 14


  Reply # 655848 15-Jul-2012 10:07 Send private message

alasta: Here is another approach that you might want to consider.

Your solution also contains latent errors, because the VLOOKUP refers to whole columns:
1. if any of the integers 1-12 are entered into the cells above D3 (perhaps after rows have been inserted), then the VLOOKUP will return incorrect results
2. if the "Lookup Table" is moved to another part of the spreadsheet, then the formulae in cells B3:B10 will not adjust to reflect the new location, so they will return incorrect results.

If the VLOOKUP formula referred only to cells D3:E14 (rather than the whole columns) then neither of these problems would occur.

Create new topic




Twitter »
Follow us to receive Twitter updates when new discussions are posted in our forums:



Follow us to receive Twitter updates when news items and blogs are posted in our frontpage:



Follow us to receive Twitter updates when tech item prices are listed in our price comparison site:





Trending now »

Hot discussions in our forums right now:

Bad lower back.
Created by TimA, last reply by TimA on 29-Jan-2015 14:04 (73 replies)
Pages... 3 4 5


Am I going down? App for the fearful of flying.
Created by networkn, last reply by Bung on 29-Jan-2015 17:26 (46 replies)
Pages... 2 3 4


I have had enough of Vodafone Customer Service... which ISP is for me?
Created by andrewinwlg, last reply by michaelmurfy on 28-Jan-2015 20:10 (23 replies)
Pages... 2


Police Speed Campaign - Summer 2014/2015
Created by nzkiwiman, last reply by blakamin on 29-Jan-2015 19:10 (143 replies)
Pages... 8 9 10


New to VDSL and wondering if I can change where the modem connects in the house
Created by Valcor, last reply by quickymart on 29-Jan-2015 20:59 (17 replies)
Pages... 2


AdBlockers on Geekzone
Created by freitasm, last reply by wally22 on 29-Jan-2015 09:55 (69 replies)
Pages... 3 4 5


Spark customers get Lightbox free for 12 months
Created by freitasm, last reply by ARK on 27-Jan-2015 16:35 (137 replies)
Pages... 8 9 10


Windows 10 News - 22 Jan
Created by Regs, last reply by joker97 on 29-Jan-2015 07:10 (131 replies)
Pages... 7 8 9



Geekzone Live »

Try automatic live updates from Geekzone directly in your browser, without refreshing the page, with Geekzone Live now.

Are you subscribed to our RSS feed? You can download the latest headlines and summaries from our stories directly to your computer or smartphone by using a feed reader.

Alternatively, you can receive a daily email with Geekzone updates.