formula help

[Maggie]

I have three columns one that is 30, one that is 60 and one that is
90. I am counting the times my day lands on 30, 60, 90 but if it
happens within the same account, then I am counting just the larger of
the three. So if I have a 30 days and a 60 days for an account, I
would pick the 60 days. Is there a formula in order for excel to
figure out which one to pick?

 

[Sandy Mann]

I find your discription a bit had to understand buy one way with

30's in Column H, 60's in Column I & 90's in Column J

Count of 30's:
=SUMPRODUCT((H1:H5=30)*(I1:I5<>60)*(J1:J5<>90))

Count of 60's:
=SUMPRODUCT((I1:I5=60)*(J1:J5<>90))

Countif 90's
=COUNTIF(J1:J5,90)

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

[email protected]
[email protected] with @tiscali.co.uk

 

[Maggie]

I find your discription a bit had to understand buy one way with

30's in Column H, 60's in Column I & 90's in Column J

Count of 30's:
=SUMPRODUCT((H1:H5=30)*(I1:I5<>60)*(J1:J5<>90))

Count of 60's:
=SUMPRODUCT((I1:I5=60)*(J1:J5<>90))

Countif 90's
=COUNTIF(J1:J5,90)

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

[email protected]
[email protected] with @tiscali.co.uk

That does not work this is what my columns look like:
A B C

30 >60 >90

1 1 1

Since the account was 30 days,60 days, and 90 days, I only want the 90
days to count instead of counting each column. Is there a way to do a
formula for that?

 

[Sandy Mann]

Hi Maggie,

That does not work this is what my columns look like:
A B C
1 1 1

Since the account was 30 days,60 days, and 90 days, I only want the 90
days to count instead of counting each column. Is there a way to do a
formula for that?

I assume that the ">30" etc are column headers and since you are using 1's
as an indicator just use that in the formula:

For the Totals of each category use:

30

60

90

=COUNTIF(C2:C10,1)
or simply
=COUNT(C2:C10)

This assumes that the 1's are real numbers and not text

If you want to count at the end of each row then use:

=IF(COUNT(A2:C2)=0,"",IF(C2,">90",IF(B2,">60",">30")))

Replacing the ">90" etc with whatever you want.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

[email protected]
[email protected] with @tiscali.co.uk