# 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

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.

Thanks
Jaco.

#### Attachments

• 24.3 KB Views: 45

#### Keebellah

Hans
No problem I can see:

I put the formula in yellow cell and it counts 2 FR

#### Attachments

• 30.5 KB Views: 33

#### Jacojs

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.

Again thanks for the help.
Jaco.

#### Keebellah

Hans
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
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

• 37.1 KB Views: 38

#### Jacojs

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
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

Aah I see.

Jaco.

#### Keebellah

Hans
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.

As Seen On