Home

Help: Excel Formula and Tables

ExcelLewis
New Contributor
Hi all,

Wondering if someone can help?

I have a excel table of data. Outside of the table I have numerous countif formula's. Example:
=COUNTIF(January_2019_Submitted_Forms[#All],"206")+COUNTIF(January_2019_Submitted_Forms[#All],"207")

I have about 20 different versions and some of the formulas contain sums of up to 10 different form names.

I would like to now copy these formula's but for a different table for February which is located on a separate sheet.
EG. = COUNTIF(February_2019_Submitted_Forms[#ALL],"206")

Is it possible to change the formula's easily or do I have to type them all out again, this time using cell references rather than the table as the reference.

Just seeing if I can do this easily and save me some time :)

Lewis
3 Replies

Hi @ExcelLewis 

 

You could do a copy paste and then highlight all the formula and replace January_2019 with February_2019  (Ctrl + H)

 

Not that I know your data but if you could keep it all in a single table called Submitted_Forms and then add an extra column for Month to flag which entries are January , February etc then reporting would be easier via Pivot Tables or COUNTIFS 

 

Hi Wyn,
Thanks for the quick response. So I tried to do a replace January with February via the “find” function however it will not do it for some reason, it says the formula is wrong.

Re your other point re data on one table, I can’t use pivots (I think) because I need to manually choose which forms to group and this changes every now and then. Ie my collection using COUNTIF is based on form types/numbers that I manually choose, so I assume you cannot manual pick what to group in a pivot?
And second would be re COUNTIF - I don’t know how to do two checks, one for the form and one for the date...? I assume that’s possible but may difficult due to number of lookups I do in one formula?
Hi

If the replace isn’t working then your tables must be named slightly differently, an extra underscore or space or spelling difference?

The COUNTIFS formula can handle multiple columns, e.g. form and date

You could add helper columns to your table that would flag which rows to include for certain groups e.g. a multiple IF statement

If you can send through a cut down version I can mock something up for you tomorrow