Solved

Sumif formula

Posted on 2013-01-03
1
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!
0
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
1 Comment
 
LVL 23

Accepted Solution

by:
NBVC earned 400 total points
ID: 38741887
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...
0

Featured Post

[Live Webinar] The Cloud Skills Gap

As Cloud technologies come of age, business leaders grapple with the impact it has on their team's skills and the gap associated with the use of a cloud platform.

Join experts from 451 Research and Concerto Cloud Services on July 27th where we will examine fact and fiction.

Question has a verified solution.

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

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…
This article describes a serious pitfall that can happen when deleting shapes using VBA.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
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…

627 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question