Solved

Sumif formula

Posted on 2013-01-03
1
391 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
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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

746 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now