Need help with an Excel formula

[strife]

I'm at my wit's end and about to pull my hair out. I'm using Excel 2010 and I need to count all of the cells which match a criteria in column C that are more than 30 days old, the dates which are in column F. All of the data is on one sheet and the total needs to report to a different sheet. I don't want to use vb and I'm not familiar with using arrays. Any wizards amongst us?

 

[mdwest]

I suck at excel.. but my wife is an absolute wizard... Ill run this by her and see what she says.... she builds macros and all sorts of formulas all day long to resolve complex accounting problems.. Im guessing she will have an idea...

 

[RaschNuts]

I am no pro but do create some crazy excel sheets to keep track of things for work. I recently had to build a Sheet for my masters pool at work. It had all the players and what they won on sheet 2 and then the teams we on sheet 1. The way it worked was on sheet 1 it searched the the player selected and then matched the prize winnings from sheet 2 to this golfer.

I am not sure if what solved my problem will work for you but if you do some searching for INDEX and MATCH you might be able to accomplish what you need.

Hope this helps.

 

[andysc83]

You can use the COUNTIF function

Try this =COUNTIF(C1:C100;"Your search criteria";F1:F100;"date criteria")

 

[strife]

You can use the COUNTIF function

Try this =COUNTIF(C1:C100;"Your search criteria";F1:F100;"date criteria")

Didn't work, this is what I have that works so far which gives me a count of all the cells in sheet "input" older than 90 days. Now if I can just figure out what to add so I can count only certain cells in column C of sheet "input" I'd be OK. I tried an array but arrays need to be specific as to a range of cells, it won't formulate complete columns and I have over 5000 rows in each column! Thanks for trying.

=COUNTIF(input!F:F,"<"&TODAY()-90)

 

[Craig Mac]

Just tell them not to be past due on their bills, problem solved :stretchgr

 

[strife]

Just tell them not to be past due on their bills, problem solved :stretchgr

Lol, actually it's for a work order report for my department.

 

[Grrrrr609]

Right click
Copy block
Open "click and ship"
Right click
Paste
Select SFR/MFR/LFR

Then yell bombs away.

Glad i could help brother.

Sent from my SPH-D710BST

 

[strife]

Right click
Copy block
Open "click and ship"
Right click
Paste
Select SFR/MFR/LFR

Then yell bombs away.

Glad i could help brother.

Sent from my SPH-D710BST

^^^This is why I have a stealth mailbox!!

 

[Grrrrr609]

Hmmmmmmmm is that soooooooo.........

Sent from my SPH-D710BST

 

[strife]

Hmmmmmmmm is that soooooooo.........

Sent from my SPH-D710BST

:stickbeat

 

[jmartins]

You can use the COUNTIF function

Try this =COUNTIF(C1:C100;"Your search criteria";F1:F100;"date criteria")

Didn't work, this is what I have that works so far which gives me a count of all the cells in sheet "input" older than 90 days. Now if I can just figure out what to add so I can count only certain cells in column C of sheet "input" I'd be OK. I tried an array but arrays need to be specific as to a range of cells, it won't formulate complete columns and I have over 5000 rows in each column! Thanks for trying.

=COUNTIF(input!F:F,"<"&TODAY()-90)

Take a picture of the spreadsheet for me. I'm visual and ill be able to tell you the formula.

Sorry can't do it from words


Sent from my iPhone using Tapatalk 2

 

[spark]

Is this what you are trying to do? Assume Column B has category 1 (yes or no in my example) and column C has dates. This function below will give you a count of all that are yes and older than 30 days

=COUNTIFS(B2:B15,"yes",C2:C15,"<"&TODAY()-30)

 

[strife]

I'll check it tomorrow when I get back to work but it does look awfully familiar to a formula I tried earlier.

 

[strife]

Is this what you are trying to do? Assume Column B has category 1 (yes or no in my example) and column C has dates. This function below will give you a count of all that are yes and older than 30 days

=COUNTIFS(B2:B15,"yes",C2:C15,"<"&TODAY()-30)

Works like a charm, thanks! I had to tweek it a bit to fit my application but I couldn't ask for more. You sir deserve a reward, pm me your address!