Excel: How to sum filled fields? Solved

Question

Hi!

I made a table in Excel, and I would like to get the total of a column two rows below the same column. But the result I want is the number of filled fields; in this column, there are rows where I haven't entered any values.

Do you know a formula (maybe IF or SI?) that can count the number of fields with values, whatever they may be?
I'd be very pleased if you have an answer for me.
THANK YOU IN ADVANCE. @+ Pierre.

Jack · Accepted Answer

Hi  Does this formula work for you?  =ROWS(C1:C8)-COUNTA(C1:C8)

hourrrah · Answer

Hi Pierre,In Excel, this kind of situation is managed with the conditional functions IF, AND, ORExample of a formula with the IF function:=IF(E5=$B$105,E5,IF(E5=$C$105,E5,IF(E5=$D$105,E5,IF(E5=$E$105,E5,IF(E5=$F$105,E5,IF(E5=$G$105,"",""))))))Best regards and @+He who seeks, finds.

blux · Answer

Hi,

you should know that when you specify a range of cells to sum in Excel, it completely disregards empty rows (which won’t affect the addition anyway).

Otherwise, there is a 'sum.if' function, which allows you to link the addition to a test:

For example:

=sum.if(A1:A5;">15")

Will only sum the cells from A1 to A5 that are greater than 15...

See you, Blux

 "Fools dare to do anything. That's how you recognize them."

Pierre · Answer

Do you happen to know the exact syntax of the formula?

For example, the result in C32 of the addition from C1 to C30

(if field filled = 1 / if field empty = 0)

Thanks again for your help.

@+ Pierre.

hourrrah · Answer

Hi Pierre,
If you want to count the number of filled cells in the C1 to C30 column, as you indicated, you use a hidden column from D1 to D30 where you'll put the result of the test =IF(C1<>"",1,0) (to copy) which you will sum up to put the total in C32

Best regards and @+
Chi cerca, trova....

banban · Answer

Hello,
There is a function COUNTIF
here is the Excel help:
COUNTIF
See also

Counts the number of cells in a range that meet a specified criterion.

Syntax

COUNTIF(range;criteria)

range refers to the range of cells in which you want to count the cells.

criteria refers to the criterion, expressed as a number, expression, or text, that determines which cells to count. For example, the criteria argument can be expressed in one of the following forms: 32, "32", ">32", or "apples".

Note

Microsoft Excel offers additional functions that you can use to analyze data based on a condition. For example, to perform a sum based on a text string or a number within a range, use the SUMIF worksheet function. To make a formula return one or two values based on a condition such as a sales bonus based on a certain sales volume, use the IF function. See Calculating a value based on a condition.

Examples

Suppose the range A3:A6 contains "apples", "oranges", "peaches", "apples":

COUNTIF(A3:A6;"apples") equals 2

Suppose the range B3:B6 contains 32, 54, 75, and 86:

COUNTIF(B3:B6;">55") equals 2

Pierre · Answer

Hi!  I stopped by to thank you all for your help.  It's nice to have a helping hand like that.  THANK YOU ALL  PS: It's JACK's formula that I kept.

hestia.79 · Answer

Great, it works. Thank you, have a good evening.

Klor El · Answer

I stumbled here by chance, and the last formula suggestion really helped me. Thank you.

CeNedra · Answer

Hello, I'm making a guest list in Excel, with a view to a mail merge, so a column for the husband, wife... and I have created my formula to count the number of guests at the same time.

I completed my formula with Jacques's :) thank you very much.

Excel: How to sum filled fields?

10 réponses

Canon mb5350 - cartridge replacement impossible

Texture issues in enshrouded

Football manager (club management game)

Select next line csh foreach

Win32 kamso then rootkit detection

Win32:spyware-gen [trj] detected by avast

Unknown video error on the freebox player

Make it or break it

How to open a cbr file

Please log in with manager privileges.