What is wrong with this IF formula

[sweetsue516]

Sample table below

The formula will be in cell f5

=IF(f1:f4)="N/A","N/A",SUMPRODUCT(f1:f4,$A1:A4))

If cells f1 through f4 equals "N/A" (which is text I put in there) then in
cell f5 show N/A, but if f1 through f4 equal all numbers then equal f1:f4
mulitiplied by a1:a4.

I will be using this formula in b5, c5,d5, e5. The actual table is bigger
and the numbers will change.

A B C D E F

1 10% 15.58 38.50 26.68 9.43 N/A

2 10% 13.51 36.03 24.85 6.41 3.34

3 4% 8.41 28.66 20.26 4.86 N/A

4 4% 3.67 1. 36 15.75 9.89 9.2

5

[Ron Coderre]

Try one of these ARRAY FORMULAS*:

F5: =IF(COUNTIF(F1:F4,"N/A")>0,"N/A",SUMPRODUCT(F1:F4,$A$1:$A$4))

or

F5: =IF(COUNT(F1:F4)<>4,"N/A",SUMPRODUCT(F1:F4,$A$1:$A$4))
This one returns N/A if any cell contains text.

Note: For array formulas, hold down [Ctrl] and [Shift] when you press
[Enter], instead of just pressing [Enter].

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"sweetsue516" wrote:

> Sample table below
>
> The formula will be in cell f5
>
> =IF(f1:f4)="N/A","N/A",SUMPRODUCT(f1:f4,$A1:A4))
>
> If cells f1 through f4 equals "N/A" (which is text I put in there) then in
> cell f5 show N/A, but if f1 through f4 equal all numbers then equal f1:f4
> mulitiplied by a1:a4.
>
> I will be using this formula in b5, c5,d5, e5. The actual table is bigger
> and the numbers will change.
>
> A B C D E F
>
> 1 10% 15.58 38.50 26.68 9.43 N/A
>
> 2 10% 13.51 36.03 24.85 6.41 3.34
>
> 3 4% 8.41 28.66 20.26 4.86 N/A
>
> 4 4% 3.67 1. 36 15.75 9.89 9.2
>
> 5
>
>
>
>

[CLR]

Maybe this will help.........

=IF(ISNA(SUMPRODUCT(F1:F4,$A$1:$A$4)),"",SUMPRODUCT(F1:F4,$A$1:$A$4))


Vaya con Dios,
Chuck, CABGx3


"sweetsue516" wrote:

> Sample table below
>
> The formula will be in cell f5
>
> =IF(f1:f4)="N/A","N/A",SUMPRODUCT(f1:f4,$A1:A4))
>
> If cells f1 through f4 equals "N/A" (which is text I put in there) then in
> cell f5 show N/A, but if f1 through f4 equal all numbers then equal f1:f4
> mulitiplied by a1:a4.
>
> I will be using this formula in b5, c5,d5, e5. The actual table is bigger
> and the numbers will change.
>
> A B C D E F
>
> 1 10% 15.58 38.50 26.68 9.43 N/A
>
> 2 10% 13.51 36.03 24.85 6.41 3.34
>
> 3 4% 8.41 28.66 20.26 4.86 N/A
>
> 4 4% 3.67 1. 36 15.75 9.89 9.2
>
> 5
>
>
>
>

[sweetsue516]

In column b through e there should be a number, then in column f there should
be a N/A.

If there is a N/A in the column N/A, if there are only numbers then
multiply that column times column A



"CLR" wrote:

> Maybe this will help.........
>
> =IF(ISNA(SUMPRODUCT(F1:F4,$A$1:$A$4)),"",SUMPRODUCT(F1:F4,$A$1:$A$4))
>
>
> Vaya con Dios,
> Chuck, CABGx3
>
>
> "sweetsue516" wrote:
>
> > Sample table below
> >
> > The formula will be in cell f5
> >
> > =IF(f1:f4)="N/A","N/A",SUMPRODUCT(f1:f4,$A1:A4))
> >
> > If cells f1 through f4 equals "N/A" (which is text I put in there) then in
> > cell f5 show N/A, but if f1 through f4 equal all numbers then equal f1:f4
> > mulitiplied by a1:a4.
> >
> > I will be using this formula in b5, c5,d5, e5. The actual table is bigger
> > and the numbers will change.
> >
> > A B C D E F
> >
> > 1 10% 15.58 38.50 26.68 9.43 N/A
> >
> > 2 10% 13.51 36.03 24.85 6.41 3.34
> >
> > 3 4% 8.41 28.66 20.26 4.86 N/A
> >
> > 4 4% 3.67 1. 36 15.75 9.89 9.2
> >
> > 5
> >
> >
> >
> >

[sweetsue516]

Ron,

I need a sum of that column times column A, if in that column there is not a
N/A.

I think the count will count how many not give me the total I need. Right??

"Ron Coderre" wrote:

> Try one of these ARRAY FORMULAS*:
>
> F5: =IF(COUNTIF(F1:F4,"N/A")>0,"N/A",SUMPRODUCT(F1:F4,$A$1:$A$4))
>
> or
>
> F5: =IF(COUNT(F1:F4)<>4,"N/A",SUMPRODUCT(F1:F4,$A$1:$A$4))
> This one returns N/A if any cell contains text.
>
> Note: For array formulas, hold down [Ctrl] and [Shift] when you press
> [Enter], instead of just pressing [Enter].
>
> Does that help?
> ***********
> Regards,
> Ron
>
> XL2002, WinXP
>
>
> "sweetsue516" wrote:
>
> > Sample table below
> >
> > The formula will be in cell f5
> >
> > =IF(f1:f4)="N/A","N/A",SUMPRODUCT(f1:f4,$A1:A4))
> >
> > If cells f1 through f4 equals "N/A" (which is text I put in there) then in
> > cell f5 show N/A, but if f1 through f4 equal all numbers then equal f1:f4
> > mulitiplied by a1:a4.
> >
> > I will be using this formula in b5, c5,d5, e5. The actual table is bigger
> > and the numbers will change.
> >
> > A B C D E F
> >
> > 1 10% 15.58 38.50 26.68 9.43 N/A
> >
> > 2 10% 13.51 36.03 24.85 6.41 3.34
> >
> > 3 4% 8.41 28.66 20.26 4.86 N/A
> >
> > 4 4% 3.67 1. 36 15.75 9.89 9.2
> >
> > 5
> >
> >
> >
> >

[Ron Coderre]

Regarding:

F5: =IF(COUNT(F1:F4)<>4,"N/A",SUMPRODUCT(F1:F4,$A$1:$A$4))

That fomula counts the numeric cells in F1:F4.

If that count is NOT 4, then there is at least one text cell...so return: N/A

Otherwise, multiply each Col_F value by its corresponding Col_A value and
sum the products.

Am I missing something? Can an you give an example?
***********
Regards,
Ron

XL2002, WinXP


"sweetsue516" wrote:

> Ron,
>
> I need a sum of that column times column A, if in that column there is not a
> N/A.
>
> I think the count will count how many not give me the total I need. Right??
>
> "Ron Coderre" wrote:
>
> > Try one of these ARRAY FORMULAS*:
> >
> > F5: =IF(COUNTIF(F1:F4,"N/A")>0,"N/A",SUMPRODUCT(F1:F4,$A$1:$A$4))
> >
> > or
> >
> > F5: =IF(COUNT(F1:F4)<>4,"N/A",SUMPRODUCT(F1:F4,$A$1:$A$4))
> > This one returns N/A if any cell contains text.
> >
> > Note: For array formulas, hold down [Ctrl] and [Shift] when you press
> > [Enter], instead of just pressing [Enter].
> >
> > Does that help?
> > ***********
> > Regards,
> > Ron
> >
> > XL2002, WinXP
> >
> >
> > "sweetsue516" wrote:
> >
> > > Sample table below
> > >
> > > The formula will be in cell f5
> > >
> > > =IF(f1:f4)="N/A","N/A",SUMPRODUCT(f1:f4,$A1:A4))
> > >
> > > If cells f1 through f4 equals "N/A" (which is text I put in there) then in
> > > cell f5 show N/A, but if f1 through f4 equal all numbers then equal f1:f4
> > > mulitiplied by a1:a4.
> > >
> > > I will be using this formula in b5, c5,d5, e5. The actual table is bigger
> > > and the numbers will change.
> > >
> > > A B C D E F
> > >
> > > 1 10% 15.58 38.50 26.68 9.43 N/A
> > >
> > > 2 10% 13.51 36.03 24.85 6.41 3.34
> > >
> > > 3 4% 8.41 28.66 20.26 4.86 N/A
> > >
> > > 4 4% 3.67 1. 36 15.75 9.89 9.2
> > >
> > > 5
> > >
> > >
> > >
> > >

[sweetsue516]

The first formulas did work. Sorry for the mis understanding.

"Ron Coderre" wrote:

> Regarding:
>
> F5: =IF(COUNT(F1:F4)<>4,"N/A",SUMPRODUCT(F1:F4,$A$1:$A$4))
>
> That fomula counts the numeric cells in F1:F4.
>
> If that count is NOT 4, then there is at least one text cell...so return: N/A
>
> Otherwise, multiply each Col_F value by its corresponding Col_A value and
> sum the products.
>
> Am I missing something? Can an you give an example?
> ***********
> Regards,
> Ron
>
> XL2002, WinXP
>
>
> "sweetsue516" wrote:
>
> > Ron,
> >
> > I need a sum of that column times column A, if in that column there is not a
> > N/A.
> >
> > I think the count will count how many not give me the total I need. Right??
> >
> > "Ron Coderre" wrote:
> >
> > > Try one of these ARRAY FORMULAS*:
> > >
> > > F5: =IF(COUNTIF(F1:F4,"N/A")>0,"N/A",SUMPRODUCT(F1:F4,$A$1:$A$4))
> > >
> > > or
> > >
> > > F5: =IF(COUNT(F1:F4)<>4,"N/A",SUMPRODUCT(F1:F4,$A$1:$A$4))
> > > This one returns N/A if any cell contains text.
> > >
> > > Note: For array formulas, hold down [Ctrl] and [Shift] when you press
> > > [Enter], instead of just pressing [Enter].
> > >
> > > Does that help?
> > > ***********
> > > Regards,
> > > Ron
> > >
> > > XL2002, WinXP
> > >
> > >
> > > "sweetsue516" wrote:
> > >
> > > > Sample table below
> > > >
> > > > The formula will be in cell f5
> > > >
> > > > =IF(f1:f4)="N/A","N/A",SUMPRODUCT(f1:f4,$A1:A4))
> > > >
> > > > If cells f1 through f4 equals "N/A" (which is text I put in there) then in
> > > > cell f5 show N/A, but if f1 through f4 equal all numbers then equal f1:f4
> > > > mulitiplied by a1:a4.
> > > >
> > > > I will be using this formula in b5, c5,d5, e5. The actual table is bigger
> > > > and the numbers will change.
> > > >
> > > > A B C D E F
> > > >
> > > > 1 10% 15.58 38.50 26.68 9.43 N/A
> > > >
> > > > 2 10% 13.51 36.03 24.85 6.41 3.34
> > > >
> > > > 3 4% 8.41 28.66 20.26 4.86 N/A
> > > >
> > > > 4 4% 3.67 1. 36 15.75 9.89 9.2
> > > >
> > > > 5
> > > >
> > > >
> > > >
> > > >

[sweetsue516]

Sorry, I miss understood! This worked great.

Thank you.

"Ron Coderre" wrote:

> Try one of these ARRAY FORMULAS*:
>
> F5: =IF(COUNTIF(F1:F4,"N/A")>0,"N/A",SUMPRODUCT(F1:F4,$A$1:$A$4))
>
> or
>
> F5: =IF(COUNT(F1:F4)<>4,"N/A",SUMPRODUCT(F1:F4,$A$1:$A$4))
> This one returns N/A if any cell contains text.
>
> Note: For array formulas, hold down [Ctrl] and [Shift] when you press
> [Enter], instead of just pressing [Enter].
>
> Does that help?
> ***********
> Regards,
> Ron
>
> XL2002, WinXP
>
>
> "sweetsue516" wrote:
>
> > Sample table below
> >
> > The formula will be in cell f5
> >
> > =IF(f1:f4)="N/A","N/A",SUMPRODUCT(f1:f4,$A1:A4))
> >
> > If cells f1 through f4 equals "N/A" (which is text I put in there) then in
> > cell f5 show N/A, but if f1 through f4 equal all numbers then equal f1:f4
> > mulitiplied by a1:a4.
> >
> > I will be using this formula in b5, c5,d5, e5. The actual table is bigger
> > and the numbers will change.
> >
> > A B C D E F
> >
> > 1 10% 15.58 38.50 26.68 9.43 N/A
> >
> > 2 10% 13.51 36.03 24.85 6.41 3.34
> >
> > 3 4% 8.41 28.66 20.26 4.86 N/A
> >
> > 4 4% 3.67 1. 36 15.75 9.89 9.2
> >
> > 5
> >
> >
> >
> >