Solved

Sumif formula

Posted on 2013-01-03

Microsoft Excel

1 solution

398 Views

Last Modified: 2013-01-03

Hi,

this is my current formula:
=SUMIFS($D$3:$D$1650,$B$3:$B$1650,$B1657,$A$3:$A$1650,"test")

I need to add column C to the above formula. Column C has dates information in the following format: 2012 WEEK 49, starting from 2012 WEEK 1. I need to sum the values in the following range: 2012 week 44 trhough 2012 week 50.

thank you!

Comment

Question by:Ladkisson

[X]

Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

Help others & share knowledge
Earn cash & points
Learn & ask questions

Join The Community

1 Comment

LVL 23

Accepted Solution

by:NBVC

NBVC earned 400 total points

ID: 387418872013-01-03

I think you need to use SUMPRODUCT() here... or extract last digits to new columns and use SUMIFS on with that column:

=SUMPRODUCT($D$3:$D$1650,($B$3:$B$1650=$B1657)*($A$3:$A$1650="test")*(RIGHT("00"&$C$3:$C$1650,2)+0>=44)*(RIGHT("00"&$C$3:$C$1650,2)+0<=50))

or in a new column:

=RIGHT("00"&$C3)+0

copied down

Then

=SUMIFS($D$3:$D$1650,$B$3:$B$1650,$B1657,$A$3:$A$1650,"test",$E$3:$E$1650,">=44",$E$3:$E$1650,"<=50")

where E is the helper column...

Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Automated Link Checker

Article by: Josh

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…

Deleting Shapes - A Warning

Article by: Martin

This article describes a serious pitfall that can happen when deleting shapes using VBA.

Creating a Scrolling Table in Excel

Video by: AnnieCushing

This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

Microsoft Excel

Enumerating Prime Numbers in Excel / VBA

Video by: Ed

Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210 (2 * 3 * 5 * 7) or 2310 (2 * 3 * 5 * 7 * 11). The larger templa…