SumIF between Sheets

S_Ko

Hi there,

I've reviewed past chats about this issue, but can't seem to resolve it on my own. NOTE this is to run SUM IF between two sheets.

Before I get into the details: Can someone please confirm that SUMIF won't sum a range based on a formula used to calculate that column data? i.e I used an IF formula to get the numbers int he Point Total.

IF that's the the case, then is there a better function set up for me to use for the following scenario?

I'm tracking various items coming in from multiple teams. Each item is has a point value assigned to it, I've created a tracking page that will calculate points based on the item submitted. This page is meant to track ALL incoming dat ( See first image). Each Chapter (team) has it's own code associated so i wouldn't have to worry about spelling and capitalization.

I have a "roll up" tracker (see the second image) that I would like to use as a the actual point collection but I was having issues with SUMIF function since the Point Total column is created via an IF function. If there is a better formula I should use, please let me know. For purpose of this, the samples are of the Striving for Excellence Column.

Thanks!

sarah

Darren Mullen

@S_Ko There are no issues with summing over a range created with a column formula.

What type of errors are you getting?

What does your formula look like?

S_Ko

@Darren Mullen

When i enter the formula I get a '0' using the column with the function in it. If i use a column where Imanually enter the number, i get the correct sum value.

the formula I'm using is: =SUMIF([Code]:[Code], Code@row, [Point Total]:[Point Total])

I have 63 different codes i'm tracking, so I'm trying to only sum the Points associated with that Code value.

thanks!

Genevieve P.

Hi @S_Ko

It sounds like you may be translating your numerical values into text values with the formula. I can see that the numbers are appearing on the left of the column which means they're being seen as Text. Do you happen to have quotes around those numbers?

For example:

=IF([This]@row = [That]@row, "5")

The "quotes" around 5 turn it into Text, which means it cannot be SUMmed. Try removing those quotes:

=IF([This]@row = [That]@row, 5)

Should you need help, please feel free to post your current formula in that [Point Total] column and we can identify where the numbers are being translated for you!

Cheers,

Genevieve

Darren Mullen

@Genevieve P. Good eye, I totally missed that! I suspect that could be the problem!

S_Ko

@Genevieve P. thank you! i didn't even think about my IF formula in that column.