Excel help

marine1

marine1

Honorary Master
Joined
Sep 4, 2006
Messages
50,636
Reaction score
3,180
Location
A black hole in the universe - JHB
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

Capture.jpg
 
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.
 
KT-B said:
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.
Click to expand...

Thanks bud will try this and play around
 
KT-B said:
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.
Click to expand...

What he said.
 
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 said:
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.
Click to expand...

Ho3n3r said:
What he said.
Click to expand...

:whistle:
 
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 ?
 
I.am.Sam said:
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 ?
Click to expand...

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:
KT-B said:
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.
Click to expand...
You are a fken JEDI !!!
 
You must log in or register to reply here.
Back
Top
Sign up to the MyBroadband newsletter
X