Inputted Goggle Formula not working?

Ask for Help
1

Hi Gurus,

Im trying to input the following ‘dynamic’ google sheets formula via the app interface I have created;

=COUNTIFS(Orders!A:A, INDIRECT(ADDRESS(ROW(), COLUMN()+11)), Orders!D:D, “>=”&DATE(YEAR(EDATE(TODAY(),-1)), MONTH(EDATE(TODAY(),-1)), 1), Orders!D:D, “<=”&DATE(YEAR(EDATE(TODAY(),-1)), MONTH(EDATE(TODAY(),-1)), EOMONTH(EDATE(TODAY(),-1),0)))

However its not presenting as a formula in the google sheet its linked to. It for some reason returns the result 0 in google sheets.

The column is set as the type “number” inside glide?

What am i doing wrong?

Why would the formula not be working?

Many thanks in advance for any light you can shed on this issue :pray:

2

What kind of interface?

3

Hey Uzo
Its a ‘text entry’ field

4

You cant add a formula using the text entry field. It will be considered as text… You can use Google Scripts to read and set that text formula as an actual formula. Can you explain why you are trying to do that? You can use arrayformula to set formula for all rows in google sheets

5

Got you … Thanks
Do you have a link to were i could learn about google scripts by any chance?
I’ve never used it?

6

No, I don’t have a link… just google it. I can write that code for you, is very simple.

7

No problem.
Problem I think I have though is that in google sheets its only displaying the number 0 after its been synced from glide after i input the formula mentioned above?

8

Because there is a text value, and your glide column is set to the number, what is shown in google sheets?

9

ok thanks

10

Can you describe more on what the formula is doing here, and do you absolutely need it in Sheets or working with Glide calculated columns would be better?

11

Hey! The formula is designed to search another sheet within the spreadsheet for relevant data and then return a numbered tally(result) to the cell. Can you explain more how I could do this with Glide calculations? Thanks in advance :pray:

12

You probably want a relation to match to the relevant records in your second table, and then a rollup through that relation to calculate a sum/total of matching records.

How do you define “relevant” in this case? (Looks to be something to do with Dates?)

If you can show screen shots of each of the two tables, we can probably give some more specific guidance.

2 Likes
13

Hey Darren! Thanks for the input :pray: Your solution definitely sounds like a great idea if it is possible. My formula searches for matching text in one column and then counts up all dates from the a specific month that the text has a record and then returns a total …. If that makes sense? I’ll do some googling on how to use the relationships feature in Glide. If you have any tips that would suit my case it would much appreciated. Thanks again :pray:

14

It kind of makes sense, but it would be much easier to visualise if you could show me a screen shot of what the data looks like, and point out the specific columns that need to be considered.

15

IMG_1321
IMG_13212532×1170 296 KB

I hope this helps. Really appreciate the help

16

Yes, that helps a lot.

Can I assume that somewhere in your App you will be expecting the user to enter a Coupon code and select a Month?

17

No the sheet your looking at is an automatic and live feed from our e-commerce store. The idea is our glide app will give us a total of the amount of times each specific coupon was used on a specific month …. If that makes sense?

18

ah, I see!

Okay, that’s easy. Do the following in the Glide Data Editor, in your Orders table:

  • Create a Math column, using the following formula: Year(Date) * 100 + Month(Date)
  • In the formula, use your Date Paid column as a replacement for Date. Set the precision to 1, and uncheck the “Use group separator” box.
  • This should give you a number the represents the month and year for each record in the format YYYYMM
  • Now create a template column that combines the Coupon column and the Math column
  • Now create a multiple relation column that matches the template column with itself.
  • Now create a rollup column that does a count of the Coupon column via the relation column.
  • The result of this last column will be the number you are after.
1 Like
19

Wow this is some wizardry thanks! :pray: Only one glitch I have is that for one of the months it is adding up all the times the coupon appears and not just for that month? Which is hard to understand because math column creates a unique number for each month? So I’m not sure what’s happening. Anyways I’ll keep tinkering and see if I can get to the bottom of it. Thanks again legend! :pray::pray::pray:

20

If you can show how you set up the math, template, relation, and rollup columns, we can see if something was missed.

2 Likes