**Question description:**

*This user has given permission to use the problem statement for this blog.*

Adding criteria to If(And( ),Sumproducts()

Solved by G. U. in 19 mins

This is the chat thread from the real Excelchat help session. It contains no private user information.

Excelchat Expert
10/08/2017 - 12:50

Hi, could you provide a data example, please

Excelchat Expert
10/08/2017 - 12:51

Are you there?

User
10/08/2017 - 12:52

hi

User
10/08/2017 - 12:52

yes

User
10/08/2017 - 12:52

i have the following formula

User
10/08/2017 - 12:53

I just need this to be able to look at multiple sections for the criteria

Excelchat Expert
10/08/2017 - 12:54

I didn't get what exactly you want me to explain you?

User
10/08/2017 - 12:54

i.e if c22 is daily and g22 is B7, but also add others with the same criteria

User
10/08/2017 - 12:54

so currently the forumla is in e3:ex

Excelchat Expert
10/08/2017 - 12:55

do you have skype?

User
10/08/2017 - 12:55

I want it to be able to see that multiple ones have criteria

User
10/08/2017 - 12:55

no sorry

Excelchat Expert
10/08/2017 - 12:55

okay, leave me your email then

Excelchat Expert
10/08/2017 - 12:55

Looks like it is not gonna be enough of this time

User
10/08/2017 - 12:55

can you point me in the right direction then?

Excelchat Expert
10/08/2017 - 12:57

I just still don't get what you want this formula to show you. That's why I need more data for the example, that i could see references of this formula

Excelchat Expert
10/08/2017 - 12:57

Then I will think it over and send you back a result via email

Excelchat Expert
10/08/2017 - 12:57

but i still don;t have any ofyour contact

User
10/08/2017 - 12:58

At the moment it is only calculating that the top result on the right is fitting the criteria of the formula

User
10/08/2017 - 12:58

i.e C22 and G22 in the formula

User
10/08/2017 - 12:58

but i need this to be able to look across the whole table 2

Excelchat Expert
10/08/2017 - 12:59

look, The C22 and G22 are BLANK. There is no Table2. I can't fix your problem without the data. You may send another data if it is classified, just change them for an example

Excelchat Expert
10/08/2017 - 12:59

it is really difficult to understand even without a real spreadsheet

Excelchat Expert
10/08/2017 - 01:00

ny enail: tieliegin@gmail.com in case the session is over, please write it down

Excelchat Expert
10/08/2017 - 01:00

*my

User
10/08/2017 - 01:00

I have put the data in the screen on the right?

Excelchat Expert
10/08/2017 - 01:00

yes, but it's not enough

User
10/08/2017 - 01:01

C22 and g22 is just the formula i am using but there is too much to send or look at

Excelchat Expert
10/08/2017 - 01:01

and the error in your formula shows me ERROR of the reference

User
10/08/2017 - 01:01

=IF(AND(Savings!C22="Daily",Savings!G22=Savings!$B$7),SUMPRODUCT(--(C6>=Savings!$E$22:$E$31)*(C6<=Savings!$F$22:$F$31),Savings!$D$22:$D$31)*365,0)

User
10/08/2017 - 01:01

all i want to know is how to add multiple fields to fit the criteria

User
10/08/2017 - 01:01

I.e more than just c22 and G22

Excelchat Expert
10/08/2017 - 01:02

but you already have used AND() function here, basically you can use it anywhere

User
10/08/2017 - 01:02

so i cant make it so it is looking at the whole table? it will only ever be able to look at c22 and g22>?

User
10/08/2017 - 01:03

as i need the first line of the formula to look at say C22:C30 and G22:30

User
10/08/2017 - 01:03

and all the ones that fit the criteria within those ranged

User
10/08/2017 - 01:03

ranges*

Excelchat Expert
10/08/2017 - 01:03

so it is not about specifying different criteria, it is about, perhaps formula of array

User
10/08/2017 - 01:03

yes

Excelchat Expert
10/08/2017 - 01:03

do you know how it works?

Excelchat Expert
10/08/2017 - 01:04

in general?

User
10/08/2017 - 01:04

the criteria itself is fine, its just its only looking at the specified cells

User
10/08/2017 - 01:04

so in my data there, it is looking at G4 and K4 and putting in one figure

User
10/08/2017 - 01:04

but i need it to look across it all

User
10/08/2017 - 01:05

ie any that fit the Daily and ISA criteria from G2:G5 and K2:K%

User
10/08/2017 - 01:05

K5*

Excelchat Expert
10/08/2017 - 01:05

try to press Ctrl+Shift+Enter to apply array

Excelchat Expert
10/08/2017 - 01:06

I got you i think, but basically I need to see this result in order to modify your formula.

User
10/08/2017 - 01:06

with the same formula?

Excelchat Expert
10/08/2017 - 01:07

i'm not sure, but try to specify range in the formula and apply Ctrl+Shift+Enter

Excelchat Expert
10/08/2017 - 01:07

I can not say you for sure just because I do not see the whole example data, sorry

Excelchat Expert
10/08/2017 - 01:09

The way we work here is that we are provided with the example data and show how the things work. Otherwise it does not work good

User
10/08/2017 - 01:09

okay

Excelchat Expert
10/08/2017 - 01:09

You may email me full info and I will help you, just don't want to to mislead you in a rush

User
10/08/2017 - 01:09

ill try the next person and try give more

User
10/08/2017 - 01:09

ok

**This is the output file from the real Excelchat help session:**

*This is an example of the expert help you can get. It contains no private user information.*