Excel - IF Function Problem - Expert Solution

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.

Get instant expert help with Excel and Google Sheets

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.