=sumifs question

[steve400243]

Hello Forum, I am trying to get this formula to sum column F, if column A has a 1 and Column N also has a 1.

=countifs(‘Receipt Report’!$F:$F,’Receipt Report’!$A:$A,”1”,’Receipt Report’!N:N,”1”)

Thank you for any help provided.

 

[Scott Huish]

First off, you don't want to put numbers in quotes, and you want SUMIFS instead of COUNTIFS:

=SUMIFS('Receipt Report'!F:F,'Receipt Report'!A:A,1,'Receipt Report'!N:N,1)

 

[dreid1011]

If you haven't tried already, remove the quotes "" from around the 1's, unless of course, they really are text values instead of numbers. Also, your title mentions =SUMIFS while your formula is =COUNTIFS... Either way, see what happens without the quotes.

 

[steve400243]

First off, you don't want to put numbers in quotes, and you want SUMIFS instead of COUNTIFS:

=SUMIFS('Receipt Report'!F:F,'Receipt Report'!A:A,1,'Receipt Report'!N:N,1)

Thanks so much Scott, This what I needed. Appreciate your time! have a nice weekend.

 

[steve400243]

If you haven't tried already, remove the quotes "" from around the 1's, unless of course, they really are text values instead of numbers. Also, your title mentions =SUMIFS while your formula is =COUNTIFS... Either way, see what happens without the quotes.

Thanks for the tips dreid! Got my answer from Scott.

 

[steve400243]

First off, you don't want to put numbers in quotes, and you want SUMIFS instead of COUNTIFS:

=SUMIFS('Receipt Report'!F:F,'Receipt Report'!A:A,1,'Receipt Report'!N:N,1)

Scott, What if the number in N would be 1, 2, or 3? And A could be different numbers also? Thank you.

 

[Scott Huish]

In that case, I would do something like this:
=SUM(IF(AND(OR('Receipt Report'!A1:A1100={1,2,3}),OR('Receipt Report'!N1:N1100={1,2,3})),'Receipt Report'!F1:F100))

Confirm with CTRL-SHIFT-ENTER rather than just Enter.

 

[steve400243]

In that case, I would do something like this:
=SUM(IF(AND(OR('Receipt Report'!A1:A1100={1,2,3}),OR('Receipt Report'!N1:N1100={1,2,3})),'Receipt Report'!F1:F100))

Confirm with CTRL-SHIFT-ENTER rather than just Enter.

Thanks Scott, I did not explain that well I think. N would be 1,2, or 3 only. and A would be an assigned container number like A123. I do not know how many, or the different number ahead of time. Sorry if i'm not explaining this very well.

 

[Scott Huish]

Something like this:

=SUMIFS('Receipt Report'!F:F,'Receipt Report'!A:A,"A123",'Receipt Report'!N:N,2)

or you could put the required value in cells and then refer to those:


=SUMIFS('Receipt Report'!F:F,'Receipt Report'!A:A,A1,'Receipt Report'!N:N,A2)

 

[steve400243]

Something like this:

=SUMIFS('Receipt Report'!F:F,'Receipt Report'!A:A,"A123",'Receipt Report'!N:N,2)

or you could put the required value in cells and then refer to those:

I greatly appreciate your help on this formula. Col A would be the same for some rows, but would be different numbers. Not just "A123". could be that for several rows, then "B456", for example, for several rows.

My desired result would be for example - the same number in col A goes for 10 rows. Column n will have a 1,2, or 3. in the next tab in cell P1 I would want the total from col F, if there is a 1 in Col N. for that matching container number in Column A.

P1 = Total for # 1 in Col N, P2 = total for # 2 in Col N, P3 = total for # 3 in Col N

Again I'm sorry if I'm not explaining it well enough.