Forum Discussion
Help: Excel Formula and Tables
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(...
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
