steve400243

Active Member
Joined
Sep 15, 2016
Messages
292
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.

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

Thank you for any help provided.
 

Some videos you may like

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,955
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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

Well-known Member
Joined
Jun 4, 2015
Messages
2,129
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

Active Member
Joined
Sep 15, 2016
Messages
292
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

Active Member
Joined
Sep 15, 2016
Messages
292

ADVERTISEMENT

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

Active Member
Joined
Sep 15, 2016
Messages
292
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

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,955
Office Version
  1. 365
  2. 2010
Platform
  1. Windows

ADVERTISEMENT

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

Active Member
Joined
Sep 15, 2016
Messages
292
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

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,955
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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)
 
Last edited:

steve400243

Active Member
Joined
Sep 15, 2016
Messages
292
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,503
Messages
5,529,248
Members
409,858
Latest member
mr20201
Top