Solved: Help with Formula please.

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Jacojs

Thread Starter
Joined
Oct 30, 2011
Messages
4
Hi.

I can't get this formula to work.Countifs(H7:HL7,"FR")

As it is it works fine but when adding a second criteria range that searches for "FR" as well I get a #value returned.

It's a simple little leave planner but I am a noob so any help will be greatly appreciated.

The spreadsheet is attached.

Thanks
Jaco.
 

Attachments

Jacojs

Thread Starter
Joined
Oct 30, 2011
Messages
4
Hi.

Thanks for the reply but I will try to explain better.

The formula that is working is currently in C81 and it reads =countifs(H7:AL7,"FR") This is just for the first employee and for January (row H7 to AL7)

When I change the formula for the same person to include February the formula reads
=countifs(H7:AL7,"FR",D13:AE13,"FR") however I dont get a value returned. This needs to be done up to December. Obviously If I can get it working for one person and one criteria the rest won't be a problem.

If anything is unclear or you need more info please let me know.

Again thanks for the help.
Jaco.
 

Keebellah

Hans
Trusted Advisor
Joined
Mar 27, 2008
Messages
6,641
I just put this in for the syntax, you will of course have to change it to cover the necessary fields.
My suggestion, and well there ra emany more since its'hadn work to create new sheetw for every year, but thtá besides the point.
I have a very simple idea and i'll out in the sheet I have here which will make it easier.

One thing, where do you want the total's displayed?
Another sheet? if you can tell me that it'll help me picture waht you want.
 

Keebellah

Hans
Trusted Advisor
Joined
Mar 27, 2008
Messages
6,641
I put in two solutions, all will take initial work, but once there it's almost maiteance free
Option 1: In colums AN through AX (I only used AN3, AO3 and AP3 y put in FR L S
in the columns below the countifs(the actual row) AN7=IF(AN$3<>"",COUNTIFS(C7:AM7,AN$3),"")
What is does is count the occurrence of row 3 in the row.
Below I add all the rows in AN for Jaco for Familty Resp, All the rows in AO for jaco, etc.
I filled in Jaco and Louis
Option 2: I used in Matthew fro January and February, but you'll have to do that for the rest.

Option 1 is more dynamic if you get a new type of leave you just add it in Row 3 AQ3 = V and you fill a V in Jaco i'tll add up right away.

I hope my explanation is not too vague and that I've been able to explain myself.

I think a macro will be much handier, but that will take a little VBA knowledg.

Let me know
 

Attachments

Jacojs

Thread Starter
Joined
Oct 30, 2011
Messages
4
Hi Keebellah.

Thank you very much!! I am using option 2, not that there is anything wrong with the first option but option 2 is simpler for me, it's a lot more work but hey it'll keep me busy :)

Just a question, why put the $ before the column and row? I found that even with it omitted the formula still works. Example $H7:$AL7 and H7:AL7 both give me the same return.

Jaco.
 

Keebellah

Hans
Trusted Advisor
Joined
Mar 27, 2008
Messages
6,641
The $ forces the reference to H
copy the formula one column to the right and you'll see what I mean
$H7:$AM7lets say H23=$H$7:$AM$7 and H24 = H7:AM7
now copy both formulas one cell to the right and one row down
I24=$H$7:$AM$7
I25=I8:AO8
 

Jacojs

Thread Starter
Joined
Oct 30, 2011
Messages
4
Aah I see.

Thanks again for your help.

This thread can be closed.

Jaco.
 

Keebellah

Hans
Trusted Advisor
Joined
Mar 27, 2008
Messages
6,641
Don't forget to 'push' the "Mark Solved" button.
Happy planning :)
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Staff online

Top