Welcome Guest.
You haven't logged in yet. If you don't have an account you can register now.

## melsan

1 post

Wannabe Geek

Topic # 105910 13-Jul-2012 16:19

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!

## mthand

148 posts

Master Geek

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?

## Ouranos

98 posts

Master Geek

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.

## Ouranos

98 posts

Master Geek

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?

## alasta

3691 posts

Uber Geek

Trusted
Subscriber

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.

## Ouranos

98 posts

Master Geek

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.

News »

Vodafone TV — television in the cloud
Posted 17-Oct-2017 19:29

Nokia 8 review: Classy midrange pure Android phone
Posted 16-Oct-2017 07:27

Why carriers might want to embrace Commerce Commission study, MVNOs
Posted 13-Oct-2017 09:42

Fitbit launches Ionic, its health and fitness smartwatch
Posted 12-Oct-2017 15:52

Xero launches machine learning automation to improve coding accuracy for small businesses
Posted 12-Oct-2017 15:45

Bank of New Zealand uses Intel AI to detect financial crime
Posted 12-Oct-2017 15:39

Sony launches Xperia XZ1, a smartphone with real-time 3D capture
Posted 11-Oct-2017 10:26

Notes on Nokia’s phone comeback
Posted 10-Oct-2017 10:06

Air New Zealand begins Inflight Wi-Fi rollout
Posted 9-Oct-2017 20:16

The latest mobile phones in perspective
Posted 9-Oct-2017 18:34

Review: Acronis True Image 2018 — serious backup
Posted 8-Oct-2017 11:22

Lenovo launches ThinkPad Anniversary Edition 25
Posted 7-Oct-2017 23:16

Less fone, more tech as Vodafone gets brand make-over
Posted 6-Oct-2017 08:16

API Talent Achieves AWS MSP Partner Status
Posted 5-Oct-2017 21:20

Stellar Consulting Group now a Domo Partner
Posted 5-Oct-2017 21:03

Geekzone Live »

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