Total number of multiples of 3 (not 6 and 9) in a given input number

[yaswanth92]

Hi all,

Can someone please help me with a formula that calculates the total number of multiples of 3 (and not 6 & 9) in a given input number?

For example: If the input is 18, the result should be 2 means multiples of 3 within 18 are 3, 6, 9, 12, 15, 18 whereas 6, 9, 12, 18 are multiples of 6 or 9. So, the result is 2 (3 and 15).

 

[shrivallabha]

Assuming that the cell A1 holds input value, you can test below formula:

=SUMPRODUCT(--(MOD(ROW($A$1:INDEX(A:A,A1)),3)=0),--(MOD(ROW($A$1:INDEX(A:A,A1)),6)<>0),--(MOD(ROW($A$1:INDEX(A:A,A1)),9)<>0))

 

[yaswanth92]

Shrivallabha,

Brilliant! I was thinking of the formula for the last 4-5hrs and got frustrated. You made my day. The formula works perfectly fine, thanks a lot.

Regards,
Yaswanth

 

[GraH - Guido]

Hi shrivallabha,

I can't write a formula like yours, but given a helper table is becomes rather easy.

 

[shrivallabha]

Hi shrivallabha,

I can't write a formula like yours, but given a helper table is becomes rather easy.

I liked your approach. It also makes the decision (inner calculation) visible as well. There's nothing special in my formula as such, it is the same unitary formula repeated thrice.