SUMIF checkbox is checked and calculate USD to EURO

MikeChap

Trying to come up with a formula in 1 cell that will sum up USD amounts in a vendor funding column, but only if the it has been approved (checkbox checked). I got the first part working:

=SUMIF([Vendor Approved]8:[Vendor Approved]44; 1; [Vendor Funding]8:[Vendor Funding]44)

However now I need to add a standard currency converter number (1 USD = 0,88040662 EUR) in the above formula. So I placed a global currency rate (0.88) in a cell "Est. Budget]6" and tried various formulas like:

=SUMIF([Vendor Approved]9:[Vendor Approved]45; 1; [Vendor Funding]9:[Vendor Funding]45 * [Est. Budget]6)

It's probably the LONG COVID brainfog thats keeping me from seeing the probably obvious solution but it's after midnight and I like to stop fiddling with this and go to sleep. Hopefully there is a Smartsheet hero here that can solve this. Much appreciated.

Summer

Hi @MikeChap

It's always the simple things that are easy to overlook. It's the main reason I post my own questions here.

Try something like this:

=SUMIF([Vendor Approved]:[Vendor Approved], [Vendor Approved]:[Vendor Approved] = 1, [Vendor Funding]:[Vendor Funding]) * [Est. Budget]$1

Range: [Vendor Approved]:[Vendor Approved]

Criterion: [Vendor Approved]:[Vendor Approved] = 1 (1 means the box is checked)

Sum Range: [Vendor Funding]:[Vendor Funding]

Closing Parenthesis and then start the new calculation with *

To save room on your sheet you can also move the .88 to the summary sheet and reference that cell in the formula.

That would look like this

=SUMIF([Vendor Approved]:[Vendor Approved], [Vendor Approved]:[Vendor Approved] = 1, [Vendor Funding]:[Vendor Funding]) * [Est. Budget]#

With the # in [Est. Budget]# taking the place of the row reference shown in the previous calculation

MikeChap

Thanks for the help Summer,

I tried :

=SUMIF([Vendor Approved]:[Vendor Approved]; [Vendor Approved]:[Vendor Approved] = 1; [Vendor Funding]:[Vendor Funding]) * [Est. Budget]6

But got a #INVALID OPERATION

MikeChap

Summer

@MikeChap

I know there are some regional differences in how smartsheet handles some operations so I'm not sure if this is one of them but in your formula you are using a ";" after the vendor approved and = 1 and in mine I am just using a ","

=SUMIF([Vendor Approved]:[Vendor Approved]; [Vendor Approved]:[Vendor Approved] = 1; [Vendor Funding]:[Vendor Funding]) * [Est. Budget]6

=SUMIF([Vendor Approved]:[Vendor Approved], [Vendor Approved]:[Vendor Approved] = 1, [Vendor Funding]:[Vendor Funding]) * [Est. Budget]#

If that's not it, please mark your question as unresolved. I hope someone in the community can help sort this out for you.

MikeChap

If I use "," the coloured (border)lines that emphasize the range do not appear. So ";" is correct. And seeing as I've copied your formula 1:1 into my sheet I'm guessing the formula just doesn't work or I haven't expressed my problem clearly enough ;-). I'll call Smartsheet in the morning. As it is now almost 2AM here I'm going to get some sleep. Maybe I'll wake up refreshed tomorrow and see what I'm doing wrong here. Thanks for the help Summer.

Dale Murphy

@MikeChap In Canada, this works for me (it selects the entire column of course, so you may need to limit it).

=SUMIF([Vendor Approved]:[Vendor Approved], =1, [Vendor Funding]:[Vendor Funding]) * [Conv Rate]$1

I think the main difference is that the multiplication by rate is moved outside the SUMIF statement.

Interesting to see what appear to be regional differences in syntax! Well, less interesting at two in the morning.

Cheers,

dm