Excel 2007 : help w/ some adv. adding and % formulas

[o811marine]

attached are 3 questions on some unusual adding formulas and getting percents from dates.

[FDibbins]

how do you plan on getting 1/5/1 from the values T S I S S S ??

how do you want to "add" the dates? eg the dates you have are 29-Mar-12 and 6-May-12, do you want to add then together (to give what?) or just combine them to give 29-Mar-12 6-May-12

you want to calc a %...of what, and based on what? tthe data you give are all dates?

[ncmay]

Try the following formulas:

Please Login or Register  to view this content.

[o811marine]

ok they work great however if want to add to that formula.
when i added to the formula it now comes up w/ 0%


=COUNTIFS(A$41:A$498,A6,B$41:B$498,B6(Table15[ATFP Comp. Date])/ROWS(Table15[ATFP Comp. Date]))

[FDibbins]

ok guys, what did i miss onthis 1, coz i just dont get it?

[ncmay]

o811marine,

It appears that the results in A3 are from counting the number of each I/S/T in range table15[C T] ~ RESULTS: 1/5/1

The results in B3 are from counting the number of empty cells and cells containing dates in range Table15[P Sub. Date] then counts the cells containing dates in range Table15[P Rec. Date] ~ RESULTS: 5/2/6


The results in C3 are from counting the cells containing dates divided by the number of rows in range Table15[ATFP Comp. Date] ~ RESULTS: 7/7 or 100%

I am a lost on your last question.

If you are trying to include additional ranges you might have to calculate them seperately.

[o811marine]

how would you combine these two formulas?
=COUNTIF(Table15[C T],"I")&"/"&COUNTIF(Table15[C T],"S")&"/"&COUNTIF(Table15[C T],"T")
and
=COUNTIFS(A$41:A$500,A6,B$41:B$500,B6)

[o811marine]

nm i got it. how ever i am haveing trouble combining these two

=COUNTIFS(A$41:A$500,A6,B$41:B$500,B6)
and
=COUNT(Table15[ATFP Comp. Date])/ROWS(Table15[ATFP Comp. Date])

[ncmay]

The formula: =COUNTIF(A$41:A$500,A6) would count the number of times the cell contents of A6 appear in range A41 to A500.

The ampersand can be used to display results in one cell from multiple formulas.

Example: =COUNTIF(A$41:A$500,A6)&"/"&COUNTIF(,B$41:B$500,B6)

[o811marine]

but how could i combine these two into one cell?
=COUNTIFS(A$41:A$500,A6,B$41:B$500,B6) =COUNT/"&COUNTIF(Table15[ATFP Comp. Date])/ROWS(Table15[ATFP Comp. Date])

what is ampersand?

[ncmay]

Ampersand = &