SUMIF after evaluating multiple Ranges

Julie Nelson

Does anyone know of a way to SUM a column in a sheet, after evaluating multiple other columns in another sheet to determine which column to sum? Essentially, I need something like an IF(SUMIF(..., but that isn't working for me.

The formula below is currently working in SHEET B to only look at a single column in SHEET B to SUM. The "97", "98", "102", etc. in the top rows of each sheet are the ID's that enable this to work. Originally there was only "Rate 1", but now we need to evaluate ID's for Rate 1, 2 and 3 to determine which "Net Monthly Rental" column to SUM.

=SUMIFS({SHEET A_Net Rental}, {SHEET A_InstallDate}, <>"", {SHEET A - Install ID}, <=[01/01/22]$1, {SHEET A_Removal ID}, >=[01/01/22]$1)

SHEET A

SHEET B

Thanks in advance for any suggestions!

Christian Graf

What qualifier are you trying to use to determine which column you want to sum? I would suggest something like this where you have multiple if conditions that chooses which sumifs to use based on your if expressions. Each possible Column sumifs would be included and chosen based on whatever qualifier you use.

=IF(SUMIFS(x) = 1, SUMIFS(sheet a, conditions), IF(SUMIFS(y) = 2, SUMIFS(sheet b, conditions), IF(SUMIFS(z) = 3, SUMIFS(sheet c, conditions), "")))