Excel help

marine1

Honorary Master
Joined
Sep 4, 2006
Messages
46,542
#1
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
 

KT-B

Honorary Master
Joined
Feb 3, 2014
Messages
12,232
#2
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
Joined
Sep 4, 2006
Messages
46,542
#3
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
 

Ho3n3r

Honorary Master
Joined
Apr 5, 2012
Messages
16,644
#5
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
Joined
Feb 3, 2014
Messages
12,232
#11
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.
 

RedViking

Honorary Master
Joined
Feb 23, 2012
Messages
11,578
#14
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.
:whistling:
 

I.am.Sam

rehabilitated troller
Joined
Jun 14, 2011
Messages
78,531
#16
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 ?
 

KT-B

Honorary Master
Joined
Feb 3, 2014
Messages
12,232
#19
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
Joined
Sep 4, 2006
Messages
46,542
#20
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 !!!
 
Top