COUNTA probs / dynamic formulas / complex(ish) SUM formula

[adeleex]

Hey peeps a few quick questions that the text books don’t seem to have the answer to!

(Have included a basic example of my workbook attached)

I have a table which has some formulas in it, every week I will paste new data under it, sometimes there will more rows, sometimes less.

• I have the formula =COUNTA(DATA) but when I paste data in it includes the blank fields as data. I have to individually select the ‘blank’ cells and press delete for it to recognize these as blanks. I have tried ‘pasting special’ as ‘values’ and have checked leave blank cells but it still considers them values

• Is it possible to have a SUM formula that only counts instances where there is a letter in another column? (in the example the formula should return 70)

• Lastly when I paste the data in for the ‘Sum of Value’ formula I have to change the data range manually, can this formula be set up to automatically select the data range that is pasted in?

Hopefully I made sense! Any help is, as always, greatly appreciated

Adele x

[Haseeb Avarakkan]

Hello, try this,

=SUMIF(A7:A65536,">=A",B7:B65536)

[ChemistB]

This doesn't differentiate between numbers, letters or words but assuming you will only have letters or nothing

=SUMIF($A$7:$A$250, "<> 0",$B$7:$B$250)
Does that work for you?

EDIT: Seems to take all values, not sure why. Use Haseeb's.

[Marcol]

Try this

Create two dynamic named ranges

Letters
Refers to :=

Please Login or Register  to view this content.

Values
Refers to :=

Please Login or Register  to view this content.

In B1

Please Login or Register  to view this content.

in B2

Please Login or Register  to view this content.

In B3

Please Login or Register  to view this content.

Hope this helps.

[adeleex]

Haseeb, tried that, works a treat! I assume A65536 will always ensure i include anything that is pasted in too, thanks muchly!!

ChemistB, cheers too!