IF/AND SUM MULTIPLE CRITERIA

[Claire Jackson]

Hi, I am trying to do an if sum with and with 6 different criteria. Can you advise where I'm going wrong please:

=IF(AND(A2="matt jackson",69,b2="not on site",70, IF(AND(A2="andy campbell",79,b2="not on site",80,IF(and(A2="kelvin green",89,b2="not on site",90))))

 

[snjpverma]

could you write in words what exactly you are trying to do here?

 

[Fluff]

Can you post a sample of your data, along with expected results.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[Claire Jackson]

I have two columns, A & B, if a = specific text and b = specific text then put in one number or another number.

Book1.xlsx
	A	B	C	D
1	kelvin green	on site	89
2	matt jackson	not on site	70
3	kelvin green	on site
4	andy campbell	on site
5	kelvin green	on site
6	matt jackson	not on site
7	andy campbell	not on site
8	matt jackson	on site
9	matt jackson	not on site
10	kelvin green	on site
11
12
13
14
15
16
17
18
19
20	matt on site	69
21	matt not on site	70
22
23	andy campbell on site	79
24	andy campbell not on site	80
25
26	kelvin green on site	89
27	kelvin green not on site	90
Sheet1

Cell Formulas
Range	Formula
C1	C1	=IF(A1="matt jackson",69,IF(A1="andy campbell",79,IF(A1="Kelvin Green",89,"none")))
C2	C2	=IF(AND(A2="matt jackson",69,"not on site",70),70,IF(AND(A2="andy campbell",79,"not on site",80),IF(AND(A2="kelvin green",79,"not on site",90),0)))

 

[Fluff]

Thanks for that, how about

+Fluff 1.xlsm
	A	B	C
1
2	kelvin green	on site	89
3	matt jackson	on site	69
4	kelvin green	on site	89
5	andy campbell	on site	79
6	kelvin green	on site	89
7	matt jackson	not on site	70
8	andy campbell	not on site	80
9	matt jackson	not on site	70
10	matt jackson	not on site	70
11	kelvin green	not on site	90
Lists

Cell Formulas
Range	Formula
C2:C11	C2	=IF(A2="matt jackson",69+(B2="not on site"), IF(A2="andy campbell",79+(B2="not on site"),IF(A2="kelvin green",89+(B2="not on site"))))

 

[Claire Jackson]

Sorry I haven't explained myself correctly:

If name is Matt Jackson and On Site then make it 69, if it's Matt Jackson and Not On Site then make it 70
If name is Andy Campbell and On Site then make it 79, if it's Andy Campbell and Not On Site then make it 80
If name is Kelvin Green and On Site then make it 89, if it's Kelvin Green and Not On Site then make it 90

 

[Fluff]

That's exactly what my suggestion does.

 

[Claire Jackson]

Sorry my apologies again, I should have pasted it into the correct row. Yes, your suggestion is absolutely correct . Thanks again.

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[Fluff]

Thinking about it, as the criteria for col B is always the same we can slim that formula down like

=IF(A2="matt jackson",69,IF(A2="andy campbell",79,IF(A2="kelvin green",89)))+(B2="not on site")