Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
Solved

# Excel formulas

Posted on 2011-03-07
Medium Priority
240 Views
Please can you help, I have annotated Columns G & H next to the formulas I am having problems with.

It is the formulas highlighted in yellow that are the ones returning the wrong result?

The formulas in column D are the same as the ones in F, but without the from and to criteria (F5:F6)

I have attached my worksheet,

Thanks,
Gaz
sun1-1--1-.xls
0
Question by:Gazza83
[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
• 2

LVL 50

Accepted Solution

barry houdini earned 1336 total points
ID: 35062991
Hello Gazza,

In F9 5 is correct because the formula is summing the values in AB for the rows with dates within your range, so it sums AB18 and gets 5, if you just want to count rows where the dates are in the range and there is a number in AB then try

=SUMPRODUCT(('Low Volume'!B14:B20000+0>=F5)*('Low Volume'!B14:B20000+0<=F6)*ISNUMBER('Low Volume'!AB14:AB20000))

For F10 2 is correct, only rows 14 and 15 qualify, why should it be 4?

F12 has a typo, try like this

=SUMPRODUCT(('Low Volume'!B14:B20000+0>=F5)*('Low Volume'!B14:B20000+0<=F6)*('Low Volume'!AC14:AC20000=TRUE))

regards, barry
0

LVL 50

Assisted Solution

barry houdini earned 1336 total points
ID: 35063015
Oh...and for the last formula in F14. It's counting 4 because it also counts "No" in I18 - did you mean to include column I?

barry
0

LVL 50

Assisted Solution

Ingeborg Hawighorst (Microsoft MVP / EE MVE) earned 664 total points
ID: 35063023
Hello,

in F9  =SUMPRODUCT(('Low Volume'!B14:B20000+0>=F5)*('Low Volume'!B14:B20000+0<=F6))

(you had a third argument that was responsible for the summing). The result is 3, if I read your data correctly

F10 calculates correctly. The result is 2

=SUMPRODUCT(('Low Volume'!B14:B20000+0>=F5)*('Low Volume'!B14:B20000+0<=F6)*('Low Volume'!AC14:AC20000="True"))

F 14 calculates correctly. There are "No" values in I18, T14, T18 and V18. Your formula includes all values from column I to V. Maybe you want T to V only?

=SUMPRODUCT(('Low Volume'!B14:B20000+0>=F5)*('Low Volume'!B14:B20000+0<=F6)*('Low Volume'!T14:V20000="No"))

That will result in 3

cheers, teylyn

0

## Featured Post

Question has a verified solution.

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

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
###### Suggested Courses
Course of the Month9 days, 9 hours left to enroll