Adding values based on varying length arrays

[davidlamb12]

Hi, I'm a bit stumped on how to approach an issue. I have a spreadsheet with about 3000 lines and I need to add values in one column based on the identifier in a previous column (the spreadsheet updates weekly, so the number of identifiers changes every week).

So, in tab #1, I have a list of numbered systems 0-500 of which I need to add figures from Tab #2.

In Tab #2, column A, I have system numbers (in random order) from 0-500 with multiple redunancies (and sometimes missing).

In Tab #2, Column C, I have figures that need to be added based on the system number.

For example, I want to add the numbers for system #1 (which is in Row 5 of Tab #1), I then want to add all the figures in tab #2, Column C that correspond to the rows where the number "1" appears in tab #2, Column A. (which may be in tab #2, rows 5, 2000, and 2310)

Functions like H/VLOOKUP will return one corresponding value, but not all of them.

Any help would be appreciated. Thank you!

 

[Andrew Poulsom]

Welcome to MrExcel.

Have a look at the SUMIF function in Excel Help.

 

[davidlamb12]

Perfect, thank you.

As a followup. I also want to count the number of instances of 2 different criteria in 2 different columns. I can't see how COUNTIF will work for me.

So if "Column A" value and "Column D" match specific criteria, I want to count the instance.

So if "Column A" = "X" and "Column D" = "5", I want to count the instances. However, if "Column A" = "X" and "Column D" = "4", I don't want to count it. Again, I don't know the number of rows, so nested "if" loops won't work. How would I attack nested COUNTIF's?

Thanks!

 

[T. Valko]

Perfect, thank you.

As a followup. I also want to count the number of instances of 2 different criteria in 2 different columns. I can't see how COUNTIF will work for me.

So if "Column A" value and "Column D" match specific criteria, I want to count the instance.

So if "Column A" = "X" and "Column D" = "5", I want to count the instances. However, if "Column A" = "X" and "Column D" = "4", I don't want to count it. Again, I don't know the number of rows, so nested "if" loops won't work. How would I attack nested COUNTIF's?

Thanks!

Try one of these...

Use cells to hold the criteria:

• F1 = X
• G1 = 5

This formula will work in any version of Excel:

=SUMPRODUCT(--(A2:A10=F1),--(D2:D10=G1))

This formula will only work in Excel 2007 and later:

=COUNTIFS(A2:A10,F1,D2:D10,G1)