Function to Use when compare sums another column

H

Heather

I have and age Field and a Column that contain counts.
If the aging Column contains a number between >=0 and <=30 I want it to sum
up the counts that correspond in field B2.

I tried the following and keep getting invalid returns.

=SUM(IF(MATCH("<=30",Sheet2!F:F,0),INDEX(Sheet2!B:B,MATCH("<=30",Sheet2!F:F)),0))
 
E

Eduardo

Hi,
try

=SUMPRODUCT(--(Sheet2!F1:F10>0),--(Sheet2!F1:F10<=30))

change range to fit your needs
 
B

Bernard Liengme

=SUMPRODUCT(--(Sheet2!F1:F1000>=0),(--(Sheet2!F1:F1000<=30),
Sheet2!B1:B1000)
You cannot use full column references with SUMPRODUCT except in Excel 2007+
And if you have Exel2007 then you could use
=SUMIFS(Sheet2!B:B,Sheet2!F:F,">=0",Sheet2!F:F,"<=30")
best wishes
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Horizontal and Vertical lookup 2
Sumif Function 1
Index Match to ignore blank cells 0
Index, match, multiple IFs query 9
Conditional Formatting on Index,Match formula 3
text match formula not working 2
Sumif with an array for criteria 3
Use TODAY() function as the criteria in a SUMIF formula 1

Top