# Excel help

#### marine1

##### Honorary Master
Hey guys I have attached the following pic below:

I am trying to automatically add the amount of days and amount of nights.
How could this be done?
Is there an easy way? Perhaps having a total for days and then a total next to it for nights then having a complete total that automatically pulls is from the days, based on colours or "D" Or "N"?

Would appreciate some input
Some time back someone here managed to do it with another type of sheet - so the formula in the top is what was used.

Thanks

#### KT-B

##### Honorary Master
There are several ways:

IF you want the two totals separately to know how many days and how many nights
=COUNTIF(B4:K4,"n") and =COUNTIF(B4:K4,"d")
first is the range that includes what you want, then the criteria.

Just to count all Ns and Ds you can use the CountIFS =COUNTIFS(B4:K4,"n",B4:K4,"d")

You could just use the CountA function too. That counts all cells that contain information.

#### marine1

##### Honorary Master
There are several ways:

IF you want the two totals separately to know how many days and how many nights
=COUNTIF(B4:K4,"n") and =COUNTIF(B4:K4,"d")
first is the range that includes what you want, then the criteria.

Just to count all Ns and Ds you can use the CountIFS =COUNTIFS(B4:K4,"n",B4:K4,"d")

You could just use the CountA function too. That counts all cells that contain information.
Thanks bud will try this and play around

#### marine1

##### Honorary Master
YOU ARE A GENIUS !!!!!

#### Ho3n3r

##### Honorary Master
There are several ways:

IF you want the two totals separately to know how many days and how many nights
=COUNTIF(B4:K4,"n") and =COUNTIF(B4:K4,"d")
first is the range that includes what you want, then the criteria.

Just to count all Ns and Ds you can use the CountIFS =COUNTIFS(B4:K4,"n",B4:K4,"d")

You could just use the CountA function too. That counts all cells that contain information.
What he said.

#### marine1

##### Honorary Master
What he said.
Worked like a bloody charm, you guys are way to sharp !!

Thanks

#### KT-B

##### Honorary Master
YOU ARE A GENIUS !!!!!
No the guys who coded Excel are :crylaugh: Glad to have helped.

#### marine1

##### Honorary Master
No the guys who coded Excel are :crylaugh: Glad to have helped.
No really, thanks bud, if I owe you anything PM me please

#### KT-B

##### Honorary Master
No really, thanks bud, if I owe you anything PM me please
lol you owe me nothing. Just have fun with Excel. It's a brilliant program.

#### marine1

##### Honorary Master
lol you owe me nothing. Just have fun with Excel. It's a brilliant program.

#### KT-B

##### Honorary Master
There are also SumIF and AverageIF functions if you ever need to add up items that meet specific criteria.
SumIFS for more than one criteria that needs to be met.

#### marine1

##### Honorary Master
There are also SumIF and AverageIF functions if you ever need to add up items that meet specific criteria.
SumIFS for more than one criteria that needs to be met.

#### KT-B

##### Honorary Master
lol for example if you wanted to add up the number of hours worked night shift, or the total wages for the night shift staff. Providing you have that data of course.

#### RedViking

##### Honorary Master
There are several ways:

IF you want the two totals separately to know how many days and how many nights
=COUNTIF(B4:K4,"n") and =COUNTIF(B4:K4,"d")
first is the range that includes what you want, then the criteria.

Just to count all Ns and Ds you can use the CountIFS =COUNTIFS(B4:K4,"n",B4:K4,"d")

You could just use the CountA function too. That counts all cells that contain information.
What he said.

#### KT-B

##### Honorary Master
It matters not. :crylaugh:

#### I.am.Sam

##### rehabilitated troller
I dont want to start a new thread

so i have a data sheet ...i want to do a pivot but it must only count a maximum of 5 rows

so one column has names, one has the date and bla bla

Some people capture 5 or less and some capture 5 or more of the same name on the sheet

What i need is a formula to say that the pivot should ignore any rows where the same name appears more than 5 times

is there something out there or am i thinking wrong ?

anyone ?

#### marine1

##### Honorary Master
Maybe message KT-B

#### KT-B

##### Honorary Master
I dont want to start a new thread

so i have a data sheet ...i want to do a pivot but it must only count a maximum of 5 rows

so one column has names, one has the date and bla bla

Some people capture 5 or less and some capture 5 or more of the same name on the sheet

What i need is a formula to say that the pivot should ignore any rows where the same name appears more than 5 times

is there something out there or am i thinking wrong ?
Do you want it to ignore all those items that have more than 5 rows or only include 5 of the rows?

I guess you would create a column before you pivot and use the if function. But what you put in it depends on your answer to the above.
I will see if there is another way to do it.

Last edited:

#### marine1

##### Honorary Master
Do you want it to ignore all those items that have more than 5 rows or only include 5 of the rows?

I guess you would create a column before you pivot and use the if function. But what you put in it depends on your answer to the above.
I will see if there is another way to do it.
You are a fken JEDI !!!