Formula question - cross sheet reference with multiple criteria

Melissa PB

Hi all,

I'm trying to calculate the total registration fees from confirmed people

=SUMIF({In-Person Registration Status}, "Confirmed",{Attendee Individual Cost}, > $0.00)

I have several needs to create cross sheet references that are looking for specific criteria and creating sums so any guidance would be much appreciated.

KPH

Hi @Melissa PB

The SUMIF function allows for one IF. If you want to look for more than one criterion then you need SUMIFS.

With SUMIF you need to specify the range to sum if it is not the range of the criterion.

If you want to use sum then the cost needs to be a number not text. This means $0.00 would be 0. You can make the cost appear with the dollar sign and the decimals in the sheet by using the currency format. In the formula, you need to use just numbers.

In your case, you could use

= SUMIF({In-Person Registration Status}, "Confirmed",{Attendee Individual Cost})

This would sum the range {Attendee Individual Cost} if the Status is Confirmed.

You only need to include the second criterion if the Cost could be less than 0 and you need to exclude negative amounts. In which case you need SUMIFS. The syntax is slightly different as the range to sum goes first.

=SUMIFS({Attendee Individual Cost}, {In-Person Registration Status}, "Confirmed", {Attendee Individual Cost}, >0)

Melissa PB

Thank you KPH, I really appreciate the explanation. Very helpful as I learn more about formulas.

You mention that I need the currency converted to a number. I created another column in the sheet and tried:

=VALUE ([Individual Cost]@row)

I'm getting #INVALID VALUE

Will do more research but if you have a suggestion, I'd appreciate it!

Thank you again,

Melissa

KPH

Your costs might already be numbers in the sheet. An easy check is to do a simple plus =col1@row + col2@row.

If the cells are numbers you will get a number back, if they are text you will get 2 bits of text, one after the other.