SUM column based on criteria of two other columns

[slam]

I've been struggling to get this to work SUMIFS or INDEX/MATCH, so I'll just explain the sheet setup.

On a Totals worksheet, I am creating the formula. In column A on this worksheet is an ID (which is derived from a UNIQUE formula, if that's relevant)

On a worksheet named Raw, the same ID exists in column C, and there can be many instances of it.
Also on this worksheet, I am looking only for instances of the word Historical that appear in column A
I then want to SUM column AF.

Both worksheets have header rows, so data starts in row 2.

This is in Excel 365

Greatly appreciate the help!

<3

 

[Fluff]

How about

=SUMIFS(Raw!AF:AF,Raw!A:A,"Historical",Raw!C:C,A2)

 

[Joe4]

SUMIFS should do it pretty easily.
Put this formula on row 2 and copy down:

=SUMIFS(Raw!AF:AF,Raw!C:C,Totals!A2,Raw!A:A,"Historical")

EDIT: I am not as fast as Fluff, but our formulas are essentially the same (it doesn't matter what order you do the criteria in).

 

[slam]

Thank you both - tried each of your formulas, and same result indeed! I don't know what I was doing wrong before....

I wish I could mark both answers as the solution!

 

[Joe4]

Thank you both - tried each of your formulas, and same result indeed! I don't know what I was doing wrong before....

I wish I could mark both answers as the solution!

You are welcome. Glad we were able to help!

No worries, Fluff posted his answer first, so I have no problem with you marking his as the solution.

 

[slam]

Hope a follow up question is ok.

I'm creating several variations of this formula now where I'm adding extra criteria. One I'm struggling with is where the year in column Z is 2024. The date format for this column is d-mmm-yy h:mm:ss

Do I need to change the formula to a SUMPRODUCT or can it still be done with SUMIFS?

 

[Joe4]

So, what exactly are you trying to do?
Return all the values that fall within a certain year?
Are you hard-coding in that year, or pulling to from a certain cell?

Regardless, you should be able to do it with a SUMPRODUCT, though there may be other options once we have a better feel for your data and desired result.

 

[slam]

Hi Joe,

If using the formula that you provided earlier, I would just add another criteria to it. I know this doesn't work, but to give you an idea of what I need:

=SUMIFS(Raw!AF:AF,Raw!C:C,Totals!A2,Raw!A:A,"Historical",Raw!Z:Z=2024)

I'd like to hard code the year in the formula.

 

[Fluff]

How about

=SUMIFS(Raw!AF:AF,Raw!C:C,A2,Raw!A:A,"Historical",Raw!Z:Z,">="&date(2024,1,1),Raw!Z:Z,"<"&date(2025,1,1))

Also it always best not to refer to the name of the sheet the formula is on.

 

[slam]

Perfect, thank you!