Complicated Problem Please help Collecting Data from a sheet to another

[can]

Hi,

=IF(AND(RAW_inapp!A150=B1, RAW_inapp!D150=$A$6), RAW_inapp!C150, "Wrong")

I use this formula.

My problem is that i have to increment this by 4 so next formulas will be ;

=IF(AND(RAW_inapp!A154=B1, RAW_inapp!D154=$A$6), RAW_inapp!C154, "Wrong")
=IF(AND(RAW_inapp!A158=B1, RAW_inapp!D158=$A$6), RAW_inapp!C158, "Wrong")
=IF(AND(RAW_inapp!A162=B1, RAW_inapp!D162=$A$6), RAW_inapp!C162, "Wrong")

Is there a way to do this?

I attached the screenshots so basically, i need to chech the rawinapp sheet and if the date and the name match, i will write it to the data sheet on the correct date. The only way i found is to do it like this by incrementing by 4 the reference value since the data is autogenerated and updating every day, it will always be in the 4th place.

Please let me know if there is a better way too. Thank you.

 

[AlanY]

this will only check Row150 and every 4th row thereafter

=IF(AND(RAW_inapp!A150=B1, RAW_inapp!D150=$A$6,MOD(ROW(RAW_inapp!A150),4)=2), RAW_inapp!C150, "Wrong")

 

[can]

this will only check Row150 and every 4th row thereafter

=IF(AND(RAW_inapp!A150=B1, RAW_inapp!D150=$A$6,MOD(ROW(RAW_inapp!A150),4)=2), RAW_inapp!C150, "Wrong")

When i put your suggestion instead of this;
=IF(AND(RAW_inapp!A150=B1, RAW_inapp!D150=$A$6), RAW_inapp!C150, "Wrong")
It gives me the correct number however when i try to get the second one to the right it gives me error.

I put an image that show what i am trying to do. I need to fill starting with B7 to C7, D7, E7 etc. It works on B7 but when i try to autofill in C7 it wont work.

Can you help?

 

[AlanY]

ok, difference approach. try this in B7, copy across (adjust ranges to suit)

=SUMPRODUCT(RAW_inapp!$C$1:$C$10000,--((RAW_inapp!$A$1:$A$10000=Sheet2!B$1)*(RAW_inapp!$D$1:$D$10000=Sheet2!$A$6)))

 

[can]

ok, difference approach. try this in B7, copy across (adjust ranges to suit)

=SUMPRODUCT(RAW_inapp!$C$1:$C$10000,--((RAW_inapp!$A$1:$A$10000=Sheet2!B$1)*(RAW_inapp!$D$1:$D$10000=Sheet2!$A$6)))

it gave me #REF!

 

[can]

ok, difference approach. try this in B7, copy across (adjust ranges to suit)

=SUMPRODUCT(RAW_inapp!$C$1:$C$10000,--((RAW_inapp!$A$1:$A$10000=Sheet2!B$1)*(RAW_inapp!$D$1:$D$10000=Sheet2!$A$6)))

Google Sheets: Sign-in

Access Google Sheets with a personal Google account or Google Workspace account (for business use).

docs.google.com

Can you reach this file? Maybe you can try this is a simple file for this try out

 

[can]

Google Sheets: Sign-in

Access Google Sheets with a personal Google account or Google Workspace account (for business use).

docs.google.com

Can you reach this file? Maybe you can try this is a simple file for this try out

Test.xlsx

drive.google.com

This link will work.

 

[Fluff]

Try removing the ref to Sheet2 in Alan's formula

=SUMPRODUCT(RAW_inapp!$C$1:$C$10000,--((RAW_inapp!$A$1:$A$10000=B$1)*(RAW_inapp!$D$1:$D$10000=$A$6)))

 

[AlanY]

it works on your test file

Test.xlsx
	A	B	C	D	E	F	G	H	I
1		01/12/2020	02/12/2020	03/12/2020	04/12/2020	05/12/2020	06/12/2020	07/12/2020	08/12/2020
2
3
4
5
6	Android_NewBuyer_1_2020
7	Conversions	107	98	94	75	75	111	107	103
Aralık 2020 New Buyer

Cell Formulas
Range	Formula
B7:I7	B7	=SUMPRODUCT(RAW_inapp!$C:$C,--((RAW_inapp!$A:$A=B$1)*(RAW_inapp!$D:$D=$A$6)))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B2:AF2	Cell Value	>=500	text	NO

 

[AlanY]

Try removing the ref to Sheet2 in Alan's formula

=SUMPRODUCT(RAW_inapp!$C$1:$C$10000,--((RAW_inapp!$A$1:$A$10000=B$1)*(RAW_inapp!$D$1:$D$10000=$A$6)))

thanks, just noticed that